CODEWITHSUNDEEP
sqlsql-serversql-delete
Zeeshan Muhammad
Zeeshan Muhammad

Reputation: 25

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

For example we have the following query that works in Oracle, however, it does not work in SQL Server. How can we rewrite it for SQL server?

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

Upvotes: 0

Views: 232

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270633

You can express this using a table-value constructor . . . in the delete:

delete from cpi
    from (values ('AD', 2010), ('AF', 2009), ('AG', 1992) ) v(countryid, year)
    where cpi.countryid = v.countryid and cpi.year = cpi.year;

No subquery is needed.

Upvotes: 0

Mike Taylor
Mike Taylor

Reputation: 161

If there are really only a few pairs, just:

 delete from cpi
 where (countryid = 'AD' and year = 2010) or (countryid = 'AF' and year = 2009) or (countryid = 'AG' and year = 1992)

Or, if there are more, maybe something like:

 delete from cpi
 where countryid + '|' + convert(varchar(4),year) in ('AD|2010','AF|2009','AG|1992')

Upvotes: 0

GarethD
GarethD

Reputation: 69789

It is a lot more clunky in comparison, but you can use a table value constructor to create your list of tuples within an EXISTS clause and match it that way, e.g.

DELETE FROM cpi
WHERE EXISTS 
      ( SELECT  1
        FROM (VALUES ('AD', 2010), ('AF', 2009), ('AG', 1992)) AS v (countryid, year)
        WHERE v.countryid = cpi.countryid
        AND v.year = cpi.year
      );

Upvotes: 1

Related Questions