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

mysql sql-delete composite-primary-key multiple-records
Ana Ban picture Ana Ban · Dec 4, 2011 · Viewed 17.6k times · Source

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

Answer

Ben picture Ben · Dec 4, 2011

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.

Related questions

MySQL Error 1093 - Can't specify target table for update in FROM clause

I have a table story_category in my database with corrupt entries. The next query returns the corrupt entries: SELECT * FROM story_category WHERE category_id NOT IN ( SELECT DISTINCT category.id FROM category INNER JOIN story_category ON category_…

Read More
How can I roll back my last delete command in MySQL?

I accidentally deleted some huge number of rows from a table... How can I roll it back? I executed the query using PuTTY. I'll be grateful if any of you can guide me safely out of this...

Read More
How to delete from multiple tables in MySQL?

I am trying to delete from a few tables at once. I've done a bit of research, and came up with this DELETE FROM `pets` p, `pets_activities` pa WHERE p.`order` > :order AND p.`pet_id` = :pet_id …

Read More