This has been drving me nuts all day so it's time to let someone tell me I'm an idiot!

I have a list of invoices in one table and a list of invoice numbers to be deleted in another table. I want to delete invoices with numbers that are in the second table.

Now, this works:

select count(*) from purchaseinvoices as a where a.invoiceno in(select b.invoiceno from deletelist as b);

and the number is correct.

But, this does not work:

delete from purchaseinvoices as a where a.invoiceno in(select b.invoiceno from deletelist as b);

It gives me:

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where a.invoiceno in(select b.invoiceno from deletelist as b)' at line 1

purchaseinvoices is a table indexed on the field recordno. deletelist is a single column table and is not indexed.

I have seen examples of this code in various forums but it doesn't work for me. what is wrong please? I am using MySQL V5.0.45

Hello!

Try this:

try this too
delete from purchaseinvoices where exists (select * from deletelistwhere invoiceno = ter.invoiceno )