CODEWITHSUNDEEP
mysql
John
John

Reputation: 13719

PHP single query Multiple UPDATE effecting AUTO_INCREMENT id instead of order_id

Here is my super-simple table layout...

id | order

1 | 1

2 | 2

I've been trying to update the order for both entries with a single query however my query tests seem to keep updating the auto_increment id field.

My goal is to make id1 = order 2 and id2 = order1 in a single query. What am I doing wrong with my query?

UPDATE forms
SET order = CASE id 
WHEN 1 THEN 2 
WHEN 2 THEN 1 
END 
WHERE id IN (1,2);

Upvotes: 0

Views: 37

Answers (1)

John Woo
John Woo

Reputation: 263703

How about doing JOIN?

UPDATE  Tablename AS a
        INNER JOIN Tablename AS b 
            ON  a.id = 1 AND b.id = 2
SET     a.order = b.order,
        b.order = a.order

Upvotes: 1

Related Questions