50

My innodb table has the following structure: 4 columns (CountryID, Year, %Change, Source), with the 2 columns (CountryID, Year) as the primary key. How do I delete multiple rows other than using a for-loop to delete each row?

I'm looking for something similar to 

DELETE FROM CPI 
 WHERE CountryID AND Year IN (('AD', 2010), ('AF', 2009), ('AG', 1992))
moodymudskipper
  • 46,417
  • 11
  • 121
  • 167
Ana Ban
  • 1,395
  • 4
  • 21
  • 29
  • Can you post the error message? What isn't working? It looks like it should work to me. Do you have dependent tables? – Carl F. Dec 04 '11 at 13:03
  • hi carl. hmm.. i just clarified my question after seeing your comment. hopefully the edit better explains it. – Ana Ban Dec 04 '11 at 13:13
  • woops, found it. edited the question to reflect the answer. thanks everyone! – Ana Ban Dec 04 '11 at 13:18
  • Yes, definitely helped me, thank you very much! BTW if someone is getting this error **#1241 - Operand should contain 2 column(s)** for me it was caused by putting the **brackets in the wrong place** like this: `DELETE FROM CPI WHERE ((CountryID, Year) IN ('AD', 2010), ('AF', 2009), ('AG', 1992))` – Timo Huovinen Jun 13 '13 at 08:35

1 Answers1

58

The answer in Oracle is:

delete from cpi
 where (countryid, year) in (('AD', 2010), ('AF', 2009), ('AG', 1992))

It's fairly standard SQL syntax and I think MySQL is the same.

Ben
  • 51,770
  • 36
  • 127
  • 149
  • 2
    thanks! tried in MySQL too and it works like a charm! – jroi_web Aug 10 '15 at 08:35
  • Thanks ,tried with mysql...this is what i was looking for. Works for me. – Swadeshi May 25 '17 at 08:19
  • tried and tested also works where param filters are returned by sub-query..very helpful...+1 for this – Dobidoo May 09 '19 at 06:57
  • What is the performance of this approach? In your example, all columns involved are part of the composite primary key. I am wondering how this query gonna perform if columns involved do not represent a composite primary key. Say I have a primary key that consists of 4 columns, but I specify only 2 columns of such PK. How long can this query be? If you have hundreds of records to be removed, this query will be extremely long... any issues with that? – Ihor M. Mar 29 '20 at 16:18
  • Hundreds of records is okay @IhorM. This'll work on billions. If you specify the first two columns of the PK then that also won't make much of a difference. – Ben Apr 24 '20 at 18:24