[SOLVED] Need MySQL Statement

[mcmuney]

I'm in need of a SQL statements that will delete all records in Table1 where the number in field B does not exist in Table2 field 2, see example below:

 

Table1

ID A B C

1 5 4 7

2 7 2 2

3 9 5 1

 

Table2

ID 1 2 3

1 A 1 C

2 B 2 A

3 C 3 G

4 H 4 S

 

Based on the example above, row3 (ID3) on Table1 should be deleted because WHERE B=5 does not exist in Table2 field 2

 

[artacus]

DELETE tab1

FROM Table1 AS tab1

LEFT JOIN Table2 AS tab2 ON tab1.ID = tab2.field2

WHERE tab2.field2 IS NULL

[mcmuney]

Thanks... worked like a charm.