Exchanging column values in Mysql

Question

I have a Mysql table with coordinates, the column names are X and Y. Now I want to change the column values in this table, so that X become Y and Y become X. The most apparent solution would be to rename the columns, but I don’t want to make changes to the structure, because I’m not necessarily allowed to do that.

This is possible with the UPDATE somehow? UPDATE SET X = Y, Y = X, obviously, won’t do what I want.

My restriction of permissions, mentioned above, effectively prevents the use of ALTER TABLE or other commands that change table structure / database. Rename columns or add new columns are unfortunately not options.

If anyone can help me.

Answer 1

Some possible possibilities

One way would be by temporary table: TEMPORARY TABLE.

How to create a temporary table:

CREATE TEMPORARY TABLE temp_table
SELECT X, Y FROM suatabela

Applying the update:

UPDATE suatabela
SET suatabela.X = temp_table.Y, suatabela.Y = temp_table.X
JOIN temp_table ON temp_table.ID = suatabela.ID

Another way (well manual), would import to excel, and "mount" the lines to a UPDATE.

---

Important

Always back up the bank before, and block access, so avoid larger problems.

Do not use VIEW, because they are only a "vision", and by changing the data, it will also be changed.

---

More about TEMPORARY TABLE: View or temporary table?

Answer 2

A solution would be to combine the two values into one and work with the CONCAT, SUBSTR and LENGTH.

UPDATE sua_tabela SET
  X = CONCAT(X, Y),
  Y = SUBSTR(X, 1, LENGTH(X) - LENGTH(Y)),
  X = SUBSTR(X, LENGTH(Y) + 1, LENGTH(X) - LENGTH(Y))

Footsteps

Suppose we have the following initial values:

X = valor1 and Y = valor2

First step would be to merge the two values into one:

X = valor1valor2 Y = valor2

To get the old value of X, just take the current value, replace the value of Y for nada and assign in Y:

X = valor1valor2 Y = valor1

Now just take it from X the value of Y:

X = valor2 Y = valor1