Assume I have a table with two column x and y without any index or primary key fig:1.0 as shown bellow:
I want to change the values of y like:
x1 = y2 x2 = y1 x3 = y4 x4 = y3 x5 = y6 x6 = y5
Pattern will be like this:
Output will look like this:
How do I do this using select query? If not possible with Select then how to do with Update or any other way in MySQL.
Advertisement
Answer
This will work only if there are no duplicate rows:
update tablename t inner join ( select (@row_number1:=@row_number1 + 1) num, x, y from tablename, (select @row_number1:=0) t ) n on n.x = t.x and n.y = t.y inner join ( select (@row_number2:=@row_number2 + 1) num, x, y from tablename, (select @row_number2:=0) t ) p on p.num = case n.num % 2 when 1 then n.num + 1 when 0 then n.num - 1 end set t.y = p.y;
See the demo.
Results:
| x | y | | --- | --- | | 11 | 22 | | 12 | 21 | | 12 | 24 | | 14 | 23 | | 15 | 26 | | 16 | 25 |