How can I iterate over all the rows of a table? (Databases)

Question

I have table A and there is one primary key identifier.

Now I want to go through all the lines in A.

I found something like "for every entry in A", but it doesn't seem to be the way you do it in MySQL.

Thing for every line. I want to take a field and convert it, paste it into another table and then update some fields of the row. I can put the selected part and insert in one statement, but I don’t know how to get the update there. So I want a loop. And for practice, I do not want to use anything other than MySQL.

change

I would appreciate an example.

And a solution that does not need to be introduced into the procedure.

change 2

think well about this scenario:

Table A and B, each with field identifiers 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 the contents of A to B using a loop.

(this is just a simplified example, of course, you would not use a loop for this). }

Answer 1

Since the loop proposal implies a request for a decision of the type of procedure. Here is mine.

Any query that works with any single record taken from the table can be wrapped in a procedure so that it is executed through each row of the table as follows:

 DROP PROCEDURE IF EXISTS ROWPERROW; DELIMITER ;; 

Then here is the procedure according to your example (table_A and table_B are 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) VALUES(ID, VAL) FROM table_A LIMIT i,1; SET i = i + 1; END WHILE; End; ;; 

Then do not forget to reset the delimiter

 DELIMITER ; 

And run the new procedure

 CALL ROWPERROW(); 

You can do whatever you want on the line "INSERT INTO", which I just copied from your sample query.

Note. ATTENTION that the line "INSERT INTO" used here reflects the line in the question. In accordance with the comments on this answer, you need to make sure that your query is syntactically correct for which version of SQL you are working for.

In the simple case, when your identifier field increases and starts with 1, the line in the example can become:

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

Replacing the string "SELECT COUNT" with

 SET n=10; 

Allows you to test your query in the first 10 record only in table_A.

Last thing. This process is also very easy to nest in different tables and was the only way I could run a process on one table that dynamically inserted different numbers of records into a new table from each row of the parent table.

If you need it to work faster, then be sure to try installing it based on, if not, then that's fine. You can also rewrite the above in cursor form, but this 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 variables that you will use in the same type as those specified in the requested tables.

My advice is to go with the questions asked when possible, and use only simple loops or cursors if you need to.

Answer 2

You really should use a set-based solution that includes two queries (main insert):

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

And for your conversion:

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

Now, if your conversion is more complex and involves multiple tables, send another question with details.

Answer 3

CURSORS are an option here, but are usually frowned because they often don't use the query mechanism. Examine SET-Based Queries to see if you can achieve what you want without using CURSOR.

Answer 4

  Use this: $stmt = $user->runQuery("SELECT * FROM tbl WHERE ID=:id"); $stmt->bindparam(":id",$id); $stmt->execute(); $stmt->bindColumn("a_b",$xx); $stmt->bindColumn("c_d",$yy); while($rows = $stmt->fetch(PDO::FETCH_BOUND)) { //---insert into new tble }