Multiple Record Delete Via Join

Hi,

I have a table with a large number of records that I need to delete, before attempt to perform the delete I also archived the records to another table.

So I need to delete all of these selected records stored in the archive table from the main table. I can now reference all the records that qualify for the delete in the main table by performing a join on the archive table like so:

select * from my_main_table a
join my_archive_table b
on a.distinct_id=b.distinct_id
and a.surrogate_key=b.surrogate_key
and a.identifier=b.indentifier

So all the records check out to be the ones I'd like to perform a delete on but I just can't figure out how to perform a delete of the records with little or no change to the existing query.

Obviously something like this won't work:

delete from my_main_table a
join my_archive_table b
on a.distinct_id=b.distinct_id
and a.surrogate_key=b.surrogate_key
and a.identifier=b.indentifier

Though it would be nice if it did.:D

So my question is how would I use the existing query with some modification to delete only the records that this query returns. I've tried selection of records in the main table based on the existing records in the archive table but it can return a higher number of records than what I know is expected. I actually need the join specified to be in place to do it.

Can anyone render any assistance on this one???

I would certainly appreciate it.

Thanks.