MySQL - phpmyadmin - swap columns

Question

I try swap 3 columns in my table. I try this :

 DELIMITER $$
CREATE PROCEDURE px()
BEGIN
  DECLARE temp VARCHAR(20);
  update `idsaccess` set 
       temp = referer,
       referer = size_var,
       size_var = agent,
       agent = temp
    WHERE  agent like '%210%' ; 
END $$

CALL p

It don't work. It give me that error: Unknown column 'temp' in 'field list' I do not understand that: temp is varchar value not a column. I also try remove DECLARE and PROCEDURE and just set variable with @. Like this:

set @temp = '';    
update `idsaccess`
    set    @temp = referer,
           referer = size_var,
           size_var = agent
           agent = @temp
    WHERE  agent like '%210%'

It don't work either. It give me. You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax. Any idea what is wrong in my code ? And to avoid misunderstanding I don't want move columns. I just want swap SOME rows (WHERE agent like '%210%') from one column to another.

Possible duplicate of [How to move columns in a MySQL table?](https://stackoverflow.com/questions/6805426/how-to-move-columns-in-a-mysql-table) — Daniel W., Apr 17 '18 at 09:09
To move columns, you make an `ALTER TABLE` statement, not a procedure. See the linked Q/A. — Daniel W., Apr 17 '18 at 09:10
you can try creating a temporary table and join to update. drop it once updation is done — bingi, Apr 17 '18 at 09:18
but i dont want move all rows in table, just some of them where agent like '%210%'. — caroline, Apr 17 '18 at 09:50
bingi : i try join, problem is that i don't have id in my table or any unique column. — caroline, Apr 17 '18 at 10:16

score 0 · Accepted Answer · answered Apr 17 '18 at 11:28

0

Try this after taking a backup... I tried for table I have with 2 columns and worked

UPDATE idsaccess   SET referer=@tmp:=referer, referer=size_var, size_var = agent, agent = @tmp  WHERE  agent like '%210%';

answered Apr 17 '18 at 11:28

bingi

78
7

MySQL - phpmyadmin - swap columns

1 Answers1