How to make DELETE query using relations

[Domac]

Hi,

I have problem deleting records from table depending on data from another
table.
So, I need to delete records where ID and YEAR is equal to ID and YEAR from
another table.

When I create query and join relation between this two fields , accees tells
me that "Operation must use an updatable query"??


Here is SQL text:

DELETE SubOrd.*
FROM SubOrd INNER JOIN ZahtjevniceSubZaBrisati ON (SubOrd.YEAR=
ZahtjevniceSubZaBrisati.YEAR)
AND (SubOrd.ID = ZahtjevniceSubZaBrisati.ID);



Thanks a lot!
Domagoj

 

[Allen Browne]

Try a subquery:

DELETE FROM SubOrd
WHERE EXISTS (
SELECT ZahtjevniceSubZaBrisati.ID
FROM ZahtjevniceSubZaBrisati
WHERE ZahtjevniceSubZaBrisati.YEAR = SubOrd.YEAR
AND ZahtjevniceSubZaBrisati.ID = SubOrd.ID);

 

[Domac]

Thanks a lot!

Try a subquery:

DELETE FROM SubOrd
WHERE EXISTS (
SELECT ZahtjevniceSubZaBrisati.ID
FROM ZahtjevniceSubZaBrisati
WHERE ZahtjevniceSubZaBrisati.YEAR = SubOrd.YEAR
AND ZahtjevniceSubZaBrisati.ID = SubOrd.ID);