delete where matches another table

[denise]

How can I delete from table called folder the records that are in table
called cancelled_folder? This is the query to select the matches:

select * from folder, cancelled_folder where folder.medrec_num =
cancelled_folder.field1

I can't get the syntax right for this and would appreciate any ideas. Thanks!

 

[Rick B]

Just a suggestion, why store this in two tables? Put it all in one table
and add a field to indicate "cancelled". It could be a checkbox or a date
field.

 

[Allen Browne]

Try a subquery:

DELETE FROM folder
WHERE EXISTS
(SELECT field1 FROM cancelled_folder
WHERE cancelled_folder.field1 = folder.medrec_num);

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

 

[denise]

Thanks very much, this worked for what I was trying to do. I wasn't using
exists.