How to delete multiple rows with 2 columns as composite primary key in MySQL?

Question

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))

Solution

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.

Comments

[Carl F.]

Can you post the error message? What isn't working? It looks like it should work to me. Do you have dependent tables?

[Ana Ban]

hi carl. hmm.. i just clarified my question after seeing your comment. hopefully the edit better explains it.

[Ana Ban]

woops, found it. edited the question to reflect the answer. thanks everyone!

[Timo Huovinen]

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))

[jroi_web]

thanks! tried in MySQL too and it works like a charm!

[Swadeshi]

Thanks ,tried with mysql...this is what i was looking for. Works for me.

[Dobidoo]

tried and tested also works where param filters are returned by sub-query..very helpful...+1 for this

[Ihor M.]

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?

[Ben]

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.