August 30, 2017 at 11:57 am
Delete from X where X.Pkey not in (select min(PKey) from X group by C, D)
OUTPUT DELETED.[PKey],
DELETED.,
DELETED.[C],
DELETED.[D],
DELETED.[E],
DELETED.[F]
INTO Y
WHERE NOT EXISTS(SELECT *
FROM Y
WHERE ( X.PKEY = Y.PKEY
and X.B = Y.B
and X.C = Y.C
and X.D = Y.D
and X.E = Y.E
and X.F = Y.F
))
When i try and rung the query above i get an error as listed below. Am i running into a limitation of the Output clause ? or is there something i am doing wrong.
Msg 102, Level 15, State 1, Line 36
Incorrect syntax near 'OUTPUT'.
Thank you for your suggestions.
August 30, 2017 at 3:30 pm
OUTPUT needs to go between the DELETE and WHERE:
Delete from X
OUTPUT DELETED.[PKey],
DELETED.,
DELETED.[C],
DELETED.[D],
DELETED.[E],
DELETED.[F]
INTO Y
where X.Pkey not in (select min(PKey) from X group by C, D)
I don't know if the OUTPUT can have it's own WHERE; I suspect not.
http://www.sqlservercentral.com/articles/T-SQL/156204/
August 31, 2017 at 10:23 am
For some reason it doesnot want to store the results in the new table, So i am just getting the results. Thank you for the help.
August 31, 2017 at 10:42 am
If you are trying to put a WHERE clause on the OUTPUT clause, you can't. You either OUTPUT the results or you don't.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply