MySQL - Find rows not in another table and store them...

MysqlOk, so I needed to gather all the rows in table A which didn't have an assigned row in table B, a little digging and I sussed it out. I needed it to be quite clever with the WHERE statement so decided to copy the info I needed into a new database table, that way I could then process them all however I needed later.

I create a table (temp_table in this case) with the data fields I wanted to keep for stage two, then wrote something like the following:

INSERT IGNORE INTO temp_table SELECT NULL as id, field_1,field_2,field_3 FROM table_1 LEFT JOIN table_2 ON table_1.id = table_2.assoc_id WHERE table_2.assoc_id IS NULL .............ADD ANY OTHER FILTERS HERE;

Depending on the size of the database its likely to be slow but it is doing its thing. I also added a unique field in temp_table so-as to not double up any info if I had to re-run the script for any reason.

I can now loop through the temp_table to do my updates and then empty it when done.

There are currently no comments to display!