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

User · Answer

Use this        stmt    user- gt runQuery  SELECT   FROM tbl WHERE ID  id         stmt- gt bindparam   id   id        stmt- gt execute              stmt- gt bindColumn  a b   xx            stmt- gt bindColumn  c d   yy         while  rows    stmt- gt fetch PDO  FETCH BOUND                   ---insert into new tble

User · Answer

Mr Purple s example I used in mysql trigger like that   begin DECLARE n INT DEFAULT 0  DECLARE i INT DEFAULT 0  Select COUNT    from user where deleted at is null INTO n  SET i 0  WHILE i lt n DO    INSERT INTO user notification notification id status userId values new notification id 1  Select userId FROM user LIMIT i 1       SET i   i   1  END WHILE  end

User · Answer

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

User · Answer

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 lt 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  quot INSERT INTO quot  line which I simply copied from your example request  Note CAREFULLY that the  quot INSERT INTO quot  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  quot SELECT COUNT quot  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

User · Answer

CURSORS are an option here  but generally frowned upon as they often do not make best use of the query engine  Consider investigating  SET Based Queries  to see if you can achieve what it is you want to do without using a CURSOR

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

Examples related to mysql

Examples related to loops