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.

Answer 1

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')

Answer 2

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'
   );