sql - SQL Server DELETE with OUTPUT not working with table alias

Question

I get an "Incorrect syntax near 'OUTPUT'" error for the OUTPUT statement. Playing with it, it seems to be that the OUTPUT statement doesn't like the table alias D. But I need that for DELETE to work with the exists clause. Any idea how to fix this?

DELETE D FROM #VARNAMES D
OUTPUT DELETED.VARNAME INTO #Table2
WHERE EXISTS (SELECT 1 FROM CIT
                WHERE RID LIKE '123%'
                AND RULES LIKE '%Storage.' + D.VARNAME + '"%')

标签： sqlsql-server-express

Answer 1

In SQL Server the OUTPUT goes before the FROM:

DELETE D 
OUTPUT DELETED.VARNAME INTO #Table2
FROM #VARNAMES D
WHERE EXISTS (SELECT 1 FROM CIT
                WHERE RID LIKE '123%'
                AND RULES LIKE '%Storage.' + D.VARNAME + '"%')
;