I have a fairly complex query that I need to run a few thousand times, with the parameter taken from the values in another table's column. It is too complex to write as a simple JOIN in any sane way.
Each execution of the query takes about 6 seconds, so writing a PHP script to perform this loop would probably just crash my web server. Is there a way I can do this in the command line in OSX? (I realize this is starting to get into Superuser territory, should I be asking it there instead?).
What I'd like to do is:
- Run a query to fetch the values that I need from
table1.columnA. - Store those values (
valueA) to an array of some sort. - Iterate over this array, and execute my complex query on each value:
INSERT INTO table2 (id, :valueA)
SELECT id from table3
WHERE id NOT IN (COMPLEX_SUBQUERY)
AND tableXYZ.value = :valueA
The best solution seems to be doing this in a shell script of some sort, but frankly Unix scripting is still somewhat of black magic to me. I'm much more comfortable scripting in PHP, but I imagine Apache will freak out if I try to do this as a PHP script.
