CODEWITHSUNDEEP
sqlsql-serverselectjoinsql-delete
Mario
Mario

Reputation: 14770

SQL DELETE FROM LEFT JOIN SELECT

I have this query in SQL Server 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.

Upvotes: 0

Views: 5484

Answers (3)

KumarHarsh
KumarHarsh

Reputation: 5094

YOU CAN ALSO USE NOT EXISTS CLAUSE,

DELETE n
    FROM [DB1].[dbo].Newsletter n
    WHERE n.DateSubscribed < '2010-01-01' AND
         NOT EXISTS (SELECT nt.UserId
                             FROM [dbo].[Newsletter_Tracking] nt.UserId= n.RecordID
                            );

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269703

Well, you need RecordId in the SELECT. An IN list only recognizes singleton values, so SELECT * generally doesn't work.

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

Your query does not do this:

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

But I suspect you want something more like this:

DELETE n
    FROM [DB1].[dbo].Newsletter n
    WHERE n.DateSubscribed < '2010-01-01' AND
          n.RecordID NOT IN (SELECT nt.UserId
                             FROM [dbo].[Newsletter_Tracking] nt
                            );

I'm not sure where the TOP 100 comes in. Your question doesn't mention it.

Upvotes: 2

jpw
jpw

Reputation: 44881

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.

Upvotes: 2

Related Questions