Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2009
    Posts
    1

    Unanswered: Deleting records matching a list in another table

    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

  2. #2
    Join Date
    Jun 2008
    Posts
    7
    Hello!

    Try this:

    Code:
    delete from purchaseinvoices where invoiceno in (select invoiceno from deletelist)

  3. #3
    Join Date
    Dec 2008
    Posts
    135
    try this too
    delete from purchaseinvoices where exists (select * from deletelistwhere invoiceno = ter.invoiceno )

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •