Deleting rows from a table based on a concatenated (composite) key

[Guest]

Hi,

I have two tables in access (e.g. t1 and t2). I am trying to delete rows
from t1 where t1.key1 and t1.key2 = t2.key1 and t2.key2. There is not a
column that is unique in either table, rather there is a concatenated primary
key based on the first two columns.

Can someone help me with this? I am trying to do it in SQL rather than
through the query wizard but either way will work.

Thanks!

 

[Allen Browne]

Your WHERE clause will need 2 parts to it. Something like this:
strSql = "DELETE FROM t1 WHERE (t1.Key1 = 99) AND (t1.Key2 = 999);"
dbEngine(0)(0).Execute strSql, dbFailOnError


If you want to delete all records from t1 that match in t2, you could use a
subquery:
strSql = "DELETE FROM t1 WHERE EXISTS " & _
"(SELECT Key1 FROM t2 WHERE " & _
"(t2.Key1 = t1.Key1) AND (t2.Key2 = t1.Key2));