DELETE records not found in another table?

[Actorial]

Hi,

I am trying to select a set of similar data in 5 tables and delete rows in another table that do not contain matches to this data. I basically want to clump all 'col1' together (with a join?) in table1,table2,table3,table4,table5, and compare these values to 'col1' in table6. It would be nice to do it in one statement, but I lack the knowledge to pull it off right now.

Here my prelim setup, but I'm stuck!

$query1 = "SELECT table1.col1, table2.col1, table3.col1, table4.col1, table5.col1 FROM table1, table2, table3, table4, table5";

$query2 = "DELETE FROM table6 WHERE (table6.col1 != table1.col1) AND (table6.col1 != table2.col1) AND (table6.col1 != table3.col1) AND (table6.col1 != table4.col1) AND (table6.col1 != table5.col1)";

Thanks For Your Help!

 

[guelphdad]

delete from table6
where table6.column1 not in
(select table1.column1 from table1
union 
select table2.column1 from table2
union 
select table3.column1 from table3
union 
select table4.column1 from table4
union 
select table5.column1 from table5
)

now that deletes rows from table 6 that dont have the value found in table1 of any one of the other tables. I'm not sure if that is what you wanted.

say table 1 has values of 1,2,3 in column 1
and table 2 has values of 3,4 in column 1

that means that all rows 1,2,3,4 will remain in table 6 because they exist in at least one of the other tables.

or did you mean delete from table 6 only when the value doesn't exist in each of the other tables?

 

[Actorial]

I think that's it. I knew I had to use NOT IN, just wasn't sure about the UNION tag. I tmakes sense tho. I'll try it and see what happens.

Thanks for your reply!