CODEWITHSUNDEEP
sqlsql-server
Dante Snow
Dante Snow

Reputation: 77

How do I delete multiple rows from a table that has 2 composite key with multiple values in SQL Server?

I have one table that contains a list o telephones from organizations. This table has 2 columns a foreign key to the table Organizations (ID) and telephone.

Telephones 

ID  Telephone   
1   1234
1   3456
1   9999
3   9999

I want to delete the fist and second rows, for doing this I have the ID value 1 and a table containing the numbers to delete ( 1234, 3456) called @TempTels

I thought of this but it didn't work :

Delete From TelephonesOrg 
where ID = ID AND Telephone = (Select Telephone from @TempTels)

Thanks in advance for any help.

Upvotes: 1

Views: 622

Answers (2)

Haji
Haji

Reputation: 2077

@Dante Snow from your code you have 3 rows having id=1..so if you want to delete first 2 row only you have to use like below..

Delete From TelephonesOrg 
where ID = 1 AND Telephone in (1234, 3456)

Upvotes: 0

marc_s
marc_s

Reputation: 755128

Since that sub-select returns more than one value, you need to use IN - not an = equals sign:

Delete From TelephonesOrg 
where ID = 1 
  AND Telephone IN (Select Telephone from @TempTels)

Upvotes: 3

Related Questions