How can I loop through all rows of a table? (MySQL)

Question

I have a table A and there is one primary key ID.

Now I want to go through all rows in A.

I found something like 'for each record in A', but this seems to be not how you do it in MySQL.

Thing is for each row I want to take a field and transform it, insert it into another table and then update some of the row's fields. I can put the select part and the insert into one statement, but I don't know how to get the update in there as well. So I want to loop. And for practice I don't want to use anything else than MySQL.

edit

I would appreciate an example.

And a solution which does not need to be put into a procedure.

edit 2

okay think of this scenario:

Table A and B, each with fields ID and VAL.

Now this is the pseudo code for what I want to do:

for(each row in A as rowA) {   insert into B(ID, VAL) values(rowA[ID], rowA[VAL]); } 

basically copying content of A into B using a loop.

(this is just a simplified example, of course you wouldn't use a loop for this.) }

Answer 1

Since the suggestion of a loop implies the request for a procedure type solution. Here is mine.

Any query which works on any single record taken from a table can be wrapped in a procedure to make it run through each row of a table like so:

First delete any existing procedure with the same name, and change the delimiter so your SQL doesn't try to run each line as you're trying to write the procedure.

DROP PROCEDURE IF EXISTS ROWPERROW; DELIMITER ;; 

Then here's the procedure as per your example (table_A and table_B used for clarity)

CREATE PROCEDURE ROWPERROW() BEGIN DECLARE n INT DEFAULT 0; DECLARE i INT DEFAULT 0; SELECT COUNT(*) FROM table_A INTO n; SET i=0; WHILE i<n DO    INSERT INTO table_B(ID, VAL) SELECT (ID, VAL) FROM table_A LIMIT i,1;   SET i = i + 1; END WHILE; End; ;; 

Then dont forget to reset the delimiter

DELIMITER ; 

And run the new procedure

CALL ROWPERROW(); 

You can do whatever you like at the "INSERT INTO" line which I simply copied from your example request.

Note CAREFULLY that the "INSERT INTO" line used here mirrors the line in the question. As per the comments to this answer you need to ensure that your query is syntactically correct for which ever version of SQL you are running.

In the simple case where your ID field is incremented and starts at 1 the line in the example could become:

INSERT INTO table_B(ID, VAL) VALUES(ID, VAL) FROM table_A WHERE ID=i; 

Replacing the "SELECT COUNT" line with

SET n=10; 

Will let you test your query on the first 10 record in table_A only.

One last thing. This process is also very easy to nest across different tables and was the only way I could carry out a process on one table which dynamically inserted different numbers of records into a new table from each row of a parent table.

If you need it to run faster then sure try to make it set based, if not then this is fine. You could also rewrite the above in cursor form but it may not improve performance. eg:

DROP PROCEDURE IF EXISTS cursor_ROWPERROW; DELIMITER ;;  CREATE PROCEDURE cursor_ROWPERROW() BEGIN   DECLARE cursor_ID INT;   DECLARE cursor_VAL VARCHAR;   DECLARE done INT DEFAULT FALSE;   DECLARE cursor_i CURSOR FOR SELECT ID,VAL FROM table_A;   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;   OPEN cursor_i;   read_loop: LOOP     FETCH cursor_i INTO cursor_ID, cursor_VAL;     IF done THEN       LEAVE read_loop;     END IF;     INSERT INTO table_B(ID, VAL) VALUES(cursor_ID, cursor_VAL);   END LOOP;   CLOSE cursor_i; END; ;; 

Remember to declare the variables you will use as the same type as those from the queried tables.

My advise is to go with setbased queries when you can, and only use simple loops or cursors if you have to.

Answer 2

You should really use a set based solution involving two queries (basic insert):

INSERT INTO TableB (Id2Column, Column33, Column44) SELECT id, column1, column2 FROM TableA  UPDATE TableA SET column1 = column2 * column3 

And for your transform:

INSERT INTO TableB (Id2Column, Column33, Column44) SELECT      id,      column1 * column4 * 100,      (column2 / column12)  FROM TableA  UPDATE TableA SET column1 = column2 * column3 

Now if your transform is more complicated than that and involved multiple tables, post another question with the details.