how to update swap values of two rows with single query

Question

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

Haim Evgi · Accepted Answer · 2012-12-16T06:43:32.437

27

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
;

edited Dec 16 '12 at 06:43

answered Dec 23 '10 at 09:15

Haim Evgi

123,187
45
217
223

What if you want to swap the last 2 rows ? – Alucard Jul 21 '14 at 15:31

score 9 · Answer 2 · edited Apr 09 '12 at 10:56

9

UPDATE my_table SET a=@tmp:=a, a=b, b=@tmp;

edited Apr 09 '12 at 10:56

Juan Mellado

14,973
5
47
54

answered Apr 09 '12 at 10:43

Rajaa

107
1
2

1

Your solution is for swapping two columns, the question was about swapping two rows. – redux Dec 05 '14 at 10:28
1

I was searching how to swap two columns, great solution :) – TMMDev Sep 03 '15 at 11:12
it's useful , and simple. – Jan 23 '17 at 08:51

how to update swap values of two rows with single query

2 Answers2

Linked