Mario M Mario M - 1 month ago 6
SQL Question

SQL DELETE FROM LEFT JOIN SELECT

I have this query in MSSQL 2012

DELETE FROM [DB1].[dbo].[Newsletter]
WHERE [DB1].[dbo].[Newsletter].RecordID IN
(SELECT TOP(100) *
FROM [DB1].[dbo].[Newsletter] LEFT JOIN [DB1].[dbo].[Newsletter_Tracking]
ON RecordId=Newsletter_Tracking.UserId
WHERE Newsletter.DateSubscribed<'2010-01-01')


The select without the delete works ok. It returns all the rows which are not in left table and which are older than 2010.

And I get this error:

Msg 116, Level 16, State 1, Line
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

jpw jpw
Answer

When you use the IN operator the subquery must return one single column/value which means you can't use select *.

In your case it should most likely be:

DELETE FROM [DB1].[dbo].[Newsletter] 
WHERE [DB1].[dbo].[Newsletter].RecordID IN (
  SELECT [DB1].[dbo].[Newsletter].RecordID
  FROM [DB1].[dbo].[Newsletter] 
  LEFT JOIN [DB1].[dbo].[Newsletter_Tracking] ON RecordId = Newsletter_Tracking.UserId
  WHERE Newsletter.DateSubscribed<'2010-01-01'
)

Also, using top without any order by clause will give you a random result - top should always by used in conjunction with an order by clause.

Comments