August 30, 2012 at 05:25
The simplest MySQL loop
Today, while working on the site, I had to separate the main directory from the additional one. And in the additional catalog it was necessary to number the necessary entries in the form "Project 1", "Project 2". And then some unknown beast did not allow me to do it quickly in some common programming language. I wanted to try, but is it possible to do this using only the means of MySQL?
As far as I remember, there are variables in MySQL, for example @a. But a web search on how to loop in MySQL didn’t give me anything.
Then I thought, because we can write
And UPDATE, in turn, goes through each record and replaces the value one at a time.
As a result, we renamed records with type 1 in the order of their ID.
PS: CONCAT concatenates strings.
As far as I remember, there are variables in MySQL, for example @a. But a web search on how to loop in MySQL didn’t give me anything.
Then I thought, because we can write
SELECT @i:=@i+1;
And UPDATE, in turn, goes through each record and replaces the value one at a time.
SELECT @i := 0;
UPDATE `table` SET `name`=CONCAT('Проект ', @i := @i+1) WHERE `type` = 1 ORDER BY `id`;
As a result, we renamed records with type 1 in the order of their ID.
PS: CONCAT concatenates strings.