Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

how to update swap values of two rows with single query

Tags:

mysql

swap

Is there a query with which i can exchange the values of two rows with single query?

like image 365
Santosh Linkha Avatar asked Dec 23 '10 09:12

Santosh Linkha


2 Answers

you can see the solution in this article

http://www.microshell.com/database/sql/swap-values-in-2-rows-sql/

look at the : The elegant way , make a join to get the data from the 2 rows to be swapped in 1 row, after that make an update is easy.

example :

UPDATE
rules AS rule1
JOIN rules AS rule2 ON
( rule1.rule_id = 1 AND rule2.rule_id = 4 )
SET
rule1.priority = rule2.priority,
rule2.priority = rule1.priority
;
like image 162
Haim Evgi Avatar answered Nov 02 '22 12:11

Haim Evgi


UPDATE my_table SET a=@tmp:=a, a=b, b=@tmp;
like image 32
Rajaa Avatar answered Nov 02 '22 12:11

Rajaa