Skip to content
Advertisement

How to change or swap value in relation with one column?

Assume I have a table with two column x and y without any index or primary key fig:1.0 as shown bellow:

enter image description here

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:

enter image description here

Output will look like this:

output image

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  |
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement