Any way to run a for loop equivalent in MySQL?

Question

I have a table that basically looks like this:

ID         Name
 1         test1
 2         test2
 n         testn

I am trying to write a query that first updates 1 row at a time, then if I am feeling greedy, updates all the rows. I tried user-defined variables like this:

SET @x = '1';
SET @name = CONCAT('test', @x);
UPDATE mytable SET Name = @name WHERE ID = @x;

But this query fails. Why is this query failing, and is it possible to improve it to something like a for loop to update every name field in the table? For the latter, is something like this possible?

<for loop>
SET @x = Name;
SET @name = CONCAT('test', @x);
UPDATE mytable SET Name = @name WHERE ID = @x;
<end loop>

Not sure why you want to change all rows to the same value - but you could consider a stored procedure which does allow loops : http://stackoverflow.com/questions/5125096/for-loop-example-in-mysql — PaulF, Nov 05 '15 at 17:24
I'm not smart enough to figure out what fails. If you mean that names do not get changed, well, you're trying to replace `test1` with `test1`! — Álvaro González, Nov 05 '15 at 17:28

score 1 · Answer 1 · answered Nov 05 '15 at 17:29

1

Anything wrong with:

  UPDATE mytable
     SET name = CONCAT('test',id)
/* WHERE id = 1 /* If you want to limit it */

answered Nov 05 '15 at 17:29

Arth

12,789
5
37
69

or `UPDATE mytable SET name = CONCAT(name,id)` :-) – Alex Nov 05 '15 at 17:38
I was trying to learn how to use user-defined variables – The One and Only ChemistryBlob Nov 05 '15 at 17:55
@TheOneandOnlyChemistryBlob that wasn't mentioned in the question, plus you won't really learn anything by using them here. Try doing a cumulative sum across a table of integers. – Arth Nov 06 '15 at 11:13

Any way to run a for loop equivalent in MySQL?

1 Answers1