CODEWITHSUNDEEP
mysqlsql
djamaile
djamaile

Reputation: 752

How to update a column twice with sql

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?

Upvotes: 1

Views: 3709

Answers (5)

Piotr Rogowski
Piotr Rogowski

Reputation: 3890

try this:

UPDATE colorpeople 
SET id = (case 
           when name = 'Blue guy' then 10   
           when name = 'Green girl' then 20 
           else id = id 
           end)

Upvotes: 4

Ruchi Patel
Ruchi Patel

Reputation: 71

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)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271231

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');

Upvotes: 0

johnp
johnp

Reputation: 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');

Upvotes: -1

RyanU
RyanU

Reputation: 130

You should try something like this :

INSERT INTO colorpeople id VALUES 10, 20
ON DUPLICATE KEY UPDATE id=VALUES(id);

Upvotes: 0

Related Questions