This is my data. Salesman is the key. I want to select them and then
change them to another Salesman.

From
Salesman Customer
25 Jeff
25 Kory
25 Jean

To
Salesman Customer
30 Jeff
30 Kory
30 Jean


How do I do that? Thanks.

Faye

Not sure what you need, but try this (make sure to try on test data not directly on production):

UPDATE Tables
SET salesman = 30
WHERE salesman = 25
AND customer IN ('Jeff', 'Kory', 'Jean');

If you need to update all customers, then this:

UPDATE Tables
SET salesman = 30
WHERE salesman = 25;

--
Plamen Ratchev
http://www.SQLStudio.com

salesman is the primary key. It can not be updated.



On Apr 15, 5:00*pm, Plamen Ratchev <Pla... (AT) SQLStudio (DOT) com> wrote:

Then I do not understand what you are trying to do. First you describe you want to change the salesman, and then that it
cannot be changed. Using DELETE and INSERT will have the same effect as UPDATE, not sure how you really need this changed.

--
Plamen Ratchev
http://www.SQLStudio.com

I was looking for options like creating a temporary table and insert
the new rows and delete the original rows using script... Wonder what
other options are available.



On Apr 16, 8:22*am, Plamen Ratchev <Pla... (AT) SQLStudio (DOT) com> wrote:

How is that different than the UPDATE statement I posted? When you delete and insert you in essence simulate an update.

--
Plamen Ratchev
http://www.SQLStudio.com

There is no rule that says that you cannot update the Primary Key.

Have you tried Plamen's query? What was the error message?

--
Gert-Jan


Faye wrote:

Plamen Ratchev (Plamen (AT) SQLStudio (DOT) com) writes:
There could be a trigger that screams blue murder if he touches the PK
in an UPDATE statement. Or if he updates the PK for more than one row at a
time. Or it could be as simple as that there is a referencing table with a
FK constraint that does not have ON UPDATE CASCADE.

In thesse case, Faye is indeed better off with an INSERT and a DELETE. And
in the case of the referencing FKs, he needs to update these, before he
deletes.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

This is exactly my situation.


On Apr 18, 4:37*pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote: