FOR LOOP with SQL Query Results

Question

I'm not a SQL expert but on occasion I run into situations where I need a for loop to iterate over the results of a query into another query. Example:

SELECT values FROM some_table WHERE name="blah";

Or in bash I might do:

mysql -e "use ${database}; SELECT values FROM some_table WHERE name='blah';" > list

Then I wind up taking the result of this query into a file and formatting it to use with php or bash like so:

for i in `cat list`; do mysql -e "use ${database}; UPDATE other_table SET column="data" where values="${i}"; done

Is there a way to accomplish this with SQL and save me time of making and formatting the list file? I guess I would need to store the results of the first query in an array and then loop through it?

--Just to clarify I'm wanting to avoid using bash, php, or any other language and get done what I'm trying to do on the mysql "interactive" command line.

George Joseph · Accepted Answer · 2019-12-17 16:55:13Z

2

It seems you want to update the other_table for all of the values which are present in some_table.?

You can accomplish that using

update other_table
   set column="data"
 where values in (select values from some_table where name='blah')

answered Dec 17, 2019 at 16:55

George Joseph

5,93213 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dstana Over a year ago

This is exactly what I was looking for. Thank you!

Raj Paliwal · Accepted Answer · 2019-12-17 19:32:12Z

0

Not sure, but, I think a subselect might be what you want.

  Select tableb.fieldname1
     from tableb
   where tableb.fieldname2 in
   (select tablea.fieldnamec
      from tablea
     where tablea.fieldnamed = 'blah'
   );

edited Dec 17, 2019 at 19:32

Raj Paliwal

9471 gold badge9 silver badges22 bronze badges

answered Dec 17, 2019 at 16:50

Marvin Krone

1

Collectives™ on Stack Overflow

FOR LOOP with SQL Query Results

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related