MySQL Forums
Forum List  »  Stored Procedures

Re: Using LOOP and cursor
Posted by: penn rabb
Date: October 05, 2017 08:55AM

Here are the table structures:

id, table2_id, number
table1 id is dynamic on every run, will always be different and there will be different amounts of rows.

id, number_column1, number_column2
table2 id is static and will always be the same on all runs.

There is really nothing to join the tables on. table1 number being between table2 number_column1 and number_column2 is the only identifier.

What I did try is a simple

UPDATE table1 SET id_table2 = SELECT id FROM table2 WHERE (SELECT number FROM table1) BETWEEN table2.number_column1 AND table2.number_column2;

This gives you the 'subquery returns more than 1 row' error.

If you direct the query to a single row in table1, there is a good result:

UPDATE table1 SET id_table2 = (SELECT id FROM table2 WHERE (SELECT number FROM table1) BETWEEN table2.number_column1 AND table2.number_column2) WHERE id = 1;

Given that, it seems I need to specify the target for the update by directing it to a row in table1, one at a time. I could export table1 ids into perl and while loop that into a query structure. But I saw this mysql loop function and thought perhaps I could do it all in the database.

If anyone can think of a way to do it straight sql without some kind of loop that directs it to a specific row, please advise.

Options: ReplyQuote

Written By
October 04, 2017 03:04PM
October 04, 2017 07:07PM
Re: Using LOOP and cursor
October 05, 2017 08:55AM
October 05, 2017 09:01AM
October 05, 2017 12:19PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.