A nested INSERT, UPDATE, DELETE, or MERGE statement must have an OUTPUT clause in UPDATE

Viewed 4580

I'm trying to update some values based on every Id in the list. The logic I have seems to be what I want.

I want to populate a temporary table of Ids. Then for every ID I want to apply this query and output the deleted date and the ID into a new table I've created.

I keep getting the error:

Msg 10716, Level 15, State 1, Line 25 A nested INSERT, UPDATE, DELETE, or MERGE statement must have an OUTPUT clause.

What does this mean? I thought I am OUTPUTTING into the new table I've created.

USE datatemp

GO


DECLARE @idlist TABLE (id INT)
INSERT INTO @idlist (id) VALUES (3009099)

DECLARE @EndDate DATETIME
SET @EndDate = '2099-12-12'

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TEMP_TABLE')
BEGIN
    CREATE TABLE  [TEMP_TABLE] (     
        [id] INT,
        [thedatetoend] DATETIME); 
END

BEGIN TRY
    SELECT  *
    FROM @idlist AS idlist
        OUTER APPLY(
                    UPDATE [custprofile]
                    SET thedatetoend = @EndDate
                    OUTPUT idlist.id, DELETED.thedatetoend
                        INTO [TEMP_TABLE]
                    FROM [custprofile] as bc
                    INNER JOIN [custinformation] as cc
                    ON cc.custengageid = bc.custengageid
                    WHERE cc.id = idlist.id
                                    AND bc.modifierid = 2
                                    AND bc.thedatetoend > GETDATE()
                                    AND cc.type = 1) o
1 Answers
Related