Exchanging column values in MySql

Navigation
-1

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 becomes Y and Y becomes X . The most apparent solution would be to rename the columns, but I do not want to make any changes to the structure, as I do not necessarily have permission to do this.

Is this possible with UPDATE in some way? UPDATE SET X = Y, Y = X obviously will not do what I want.

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

If anyone can help me.

    
asked by anonymous 21.09.2018 / 21:06

2 answers

1

Some possible possibilities

A form would be per temporary table: TEMPORARY TABLE .

How to create a temporary table: 

CREATE TEMPORARY TABLE temp_table
SELECT X, Y FROM suatabela

Applying 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 be to import to excel, and "mount" the rows to a UPDATE .

Important

Always back up the database before, and block access, thus avoiding major problems.

Do not use VIEW , as they are just a "view", and changing the data will also change.

More about TEMPORARY TABLE : View or temporary table?

    
21.09.2018 / 21:42
0

One solution would be to merge the two values into one and work with 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 join 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 with nada and assign in Y :

  

X = valor1valor2 Y = valor1

Now just get X out of Y :

  

X = valor2 Y = valor1

    
21.09.2018 / 21:57
How to make a print in VBA My img tag does not respond to my css settings