SQL Query - Subquery

[Mona]

Hi!

I'm trying to do something like this:

DELETE * FROM Table1 WHERE id1, id2 IN (SELECT id1,
id2 FROM Table2);

But Access does not support this query! I tried these one to:

This one asks me for the value for id2:
DELETE * FROM Table1 WHERE id1 IN (SELECT id1 FROM
Table2 where Table1.id2 = Table2.id2)

This one ask me the values for id1 and id2:
DELETE * FROM Table1 WHERE Exists (SELECT * FROM Table2 where
Table1.id1 = Table2.id1 and Table1.id2 = Table2.id2)

This one CAN'T delete, because there is a join:
DELETE * FROM Table1 inner join (SELECT id1,
id2 FROM Table2) on (Table1.id2 = Table2.id2 and
Table1.id1 = Table2.id1)

So nothing is working!

Please somebody Help!!

Thanks

 

[John Vinson]

Hi!

I'm trying to do something like this:

DELETE * FROM Table1 WHERE id1, id2 IN (SELECT id1,
id2 FROM Table2);

But Access does not support this query! I tried these one to:

Try

DELETE * FROM Table1
WHERE ID1 IN (SELECT Id1 FROM Table2)
AND ID2 IN (SELECT Id2 FROM Table2);

Or, if you have a unique two-field Index on ID1 and ID2,

DELETE Table1.*
FROM Table1
INNER JOIN Table2
ON Table1.ID1 = Table2.ID1 AND Table1.ID2 = Table2.ID2;

John W. Vinson[MVP]

 

[Van T. Dinh]

If you are trying to delete Records in Table1 that have "matching" Records
in Table2, i.e. Table1.id1 = Table2.id1 AND Table1.id2 = Table2.id2, try:

********
DELETE * FROM Table1 AS T1
WHERE EXISTS
(
SELECT * FROM Table2 AS T2
WHERE (T2.id1 = T1.id1)
AND (T2.id2 = T1.id2)
)
********