can one delete records based on a join?

[mrvy1]

One would think that this would be a fairly common task. If there are any records in Table A whose id is on the "kill list" (Table B), delete that record.

So, for example:

DELETE *
FROM Table_A
WHERE Table_A.ID = Table_B_List_of_bogus_records.ID;

I'm attempting this query on Access, so it may not be a SQL problem. Access views "Table_B_List_of_bogus_records.ID" as a parameter needing input.

Any ideas?

Much appreciated.

[skhanal]

Have you tried this

DELETE Table_A
FROM Table_A, Table_B_List_of_bogus_records
WHERE Table_A.ID = Table_B_List_of_bogus_records.ID;

[mrvy1]

skhanal,

I get a message saying:

"cannot find file <path to my database>\Table_B_List_of_bogus_records"

I don't get it...

But thanks for the suggestion anyway.

---Michael

[mrvy1]

I've got it to work after all. I tried this query:

DELETE Table_A.*
FROM Table_A
INNER JOIN Table_B_List_of_bogus_records ON Table_A.ID = Table_B_List_of_bogus_records.ID ;

Seems rather convoluted IMHO, but it works.

Thanks!

[skhanal]

Oh ya, Access does not recognize the syntax I wrote, it had to be ANSI.