1

I have a table with these values: 

Name       |  id
----------------
Blue guy   |  20
Green girl |  10

The id column is a unique value. What I want is to update blue guy id to 10 and in that same query I want to update green girl id to 20. My current SQL query is: 

UPDATE colorpeople SET id = 20
WHERE name = 'Blue guy';

Where can I specify to update the green girl to?

Paul Karam
  • 4,052
  • 8
  • 30
  • 53
djamaile
  • 695
  • 3
  • 12
  • 30
  • Take a look at this question and the accepted answer: https://stackoverflow.com/questions/20255138/sql-update-multiple-records-in-one-query – Paul Karam Jun 16 '17 at 09:34
  • logically it's two update statements because it's two different values with different conditions attached. But you can combine it into one statement as per the link given just above. It only really scales to two or three values though, if you need to do it for large numbers of rows it'll get unwieldy. – ADyson Jun 16 '17 at 09:35
  • Typically, this kind of thing is done in a 3-step transaction - 1. Update 20 to x. 2. Update 10 to 20. 3. Update x to 10. – Strawberry Jun 16 '17 at 09:45

5 Answers5

4

try this:

UPDATE colorpeople 
SET id = (case 
           when name = 'Blue guy' then 10   
           when name = 'Green girl' then 20 
           else id = id 
           end)
Paul Karam
  • 4,052
  • 8
  • 30
  • 53
Piotr Rogowski
  • 3,642
  • 19
  • 24
1

Please try below script :

ALTER TABLE colorpeople DROP CONSTRAINT PK_colorpeople

UPDATE colorpeople SET ID = 10 WHERE Name = 'Blue guy'
UPDATE colorpeople SET ID = 20 WHERE Name = 'Green girl'

ALTER TABLE colorpeople ADD CONSTRAINT PK_colorpeople PRIMARY KEY /* CLUSTERED */ (ID)
ADyson
  • 57,178
  • 14
  • 51
  • 63
Ruchi Patel
  • 71
  • 1
  • 9
0

You should try something like this :

INSERT INTO colorpeople id VALUES 10, 20
ON DUPLICATE KEY UPDATE id=VALUES(id);
RyanU
  • 128
  • 1
  • 8
0

The best way of doing this uses a WHERE clause:

UPDATE colorpeople 
    SET id = (case when name = 'Blue guy' then 10
                   when name = 'Green girl' then 20
              end)
    WHERE name in ('Blue guy', 'Green girl');
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
-1

if you want to update a unique key that already exists

UPDATE colorpeople SET id = -20 WHERE naam = 'Blue guy';
UPDATE colorpeople SET id = -10 WHERE naam = 'Green girl';
UPDATE colorpeople SET id = -id WHERE naam in ('Blue guy', 'Green girl');
johnp
  • 1
  • 3