CODEWITHSUNDEEP
mysqlsql
Paul
Paul

Reputation: 13

Sql not updating elements in proper order

I have an animals table and am trying to update the type of all dogs to be cats, and then make the existing cats to be dogs.

So far I have 

UPDATE Animals
SET name='Cat' 
WHERE name = 'Dog' 
   SET name = 'Dog'
   WHERE name = 'Cat';

Upvotes: 0

Views: 38

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270683

You can handle both scenarios in one query:

UPDATE Animals SET name = (CASE WHEN name = 'Dog' THEN 'Cat' ELSE 'Dog' END)
   WHERE name IN ('Cat', 'Dog');

Upvotes: 5

Sablefoste
Sablefoste

Reputation: 4192

Try three queries:

UPDATE Animals SET name = 'Animal' WHERE name='Dog';
UPDATE Animals SET name = 'Dog' WHERE name='Cat';
UPDATE Animals SET name = 'Cat' WHERE name='Animal';

Upvotes: 0

Related Questions