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.

To move columns, you make an ALTER TABLE statement, not a procedure. See the linked Q/A. — Daniel W.
– Daniel W., Commented Apr 17, 2018 at 9:10
you can try creating a temporary table and join to update. drop it once updation is done — bingi
– bingi, Commented Apr 17, 2018 at 9:18
but i dont want move all rows in table, just some of them where agent like '%210%'. — caroline
– caroline, Commented Apr 17, 2018 at 9:50
bingi : i try join, problem is that i don't have id in my table or any unique column. — caroline
– caroline, Commented Apr 17, 2018 at 10:16

bingi · Accepted Answer · 2018-04-17 11:28:12Z

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, 2018 at 11:28

bingi

787 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL - phpmyadmin - swap columns

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related