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