MySql using user defined variables in an update statement

Question

How can I use user defined variables in a MySql update statement? By user defined variables, i'm referring to the @v:=.

The statement below works fine without the user defined variables, but not with them.

update amga a left join amgb b on a.itemTempId = b.itemTempId
set @i:= concat(a.itemCountry,'-',a.id), a.itemId = @i, b.itemId = @i 
where a.itemId is null or b.itemId is null;

I'll be using this with php PDO later.

This works, but does use user defined variables

update amga a left join amgb b on a.itemTempId = b.itemTempId
set a.itemId = concat(a.itemCountry,'-',a.id), b.itemId = concat(a.itemCountry,'-',a.id) 
where a.itemId is null or b.itemId is null;

valex · Accepted Answer · 2014-09-09 07:50:23Z

2

Try following syntax:

update amga a left join amgb b on a.itemTempId = b.itemTempId
set a.itemId = (@i:= concat(a.itemCountry,'-',a.id)), b.itemId = @i 
where a.itemId is null or b.itemId is null;

SQLFiddle demo

answered Sep 9, 2014 at 7:50

valex

24.2k7 gold badges47 silver badges62 bronze badges

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

1 Comment

Xenos Over a year ago

You can avoid the user variable here AFAIK, with set a.itemId = concat(a.itemCountry,'-',a.id), b.itemId = a.itemId since MySQL updates use the updated values in the next "SET" assignments

Collectives™ on Stack Overflow

MySql using user defined variables in an update statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related