0

My table Salary has this structure:

EmpId Salary
-------------
1     3000
2     4000
3     5000
4     5000
5     8000
6     2000

I am trying to do the delete duplicate record but it is failing with the below query:

BEGIN TRAN

WITH CTE_BASE AS
(
    SELECT
        (ROW_NUMBER() OVER (PARTITION BY SALARY ORDER BY SALARY DESC)) AS rownum
    FROM
        Salary
)
--SELECT ROWNUM FROM CTE_BASE
DELETE FROM CTE_BASE 
WHERE ROWNUM > 1

ROLLBACK TRAN

I get this error:

Msg 156, Level 15, State 1, Line 21
Incorrect syntax near the keyword 'AS'

The commented out SELECT statement works though.

Is the CTE not scoped to single delete?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Shalini Raj
  • 177
  • 2
  • 19
  • 3
    `begin tran;` <— notice semicolon – lptr Jul 19 '22 at 15:11
  • @lptr great, but i still am confused as to how come select is working though ? – Shalini Raj Jul 19 '22 at 15:13
  • without the semicolon, and after the begin tran, what probably happens is sql server assumes `with cte_base.....` is the name of the transaction. Not sure where it assumes the end, but it could well be at the two dashes – George Menoutis Jul 19 '22 at 15:17
  • @lptr could not get a clear error in SSMS as in the link which you shared . Is it possible to get in v18.9.2 or any other IDE ? – Shalini Raj Jul 19 '22 at 15:31
  • 1
    It isn't possible to get a "clear error" in this case. SSMS is not to blame, it's SQL Server that's formatting the error message. Your statement effectively ends up parsed as `BEGIN TRANSACTION WITH CTE_BASE AS...` and this errors on the fact that `AS` cannot be parsed as a string literal. The proper syntax is actually `BEGIN TRANSACTION [name] WITH MARK 'description'`, but the parser ignores both the missing name, and the fact that `CTE_BASE` isn't `MARK` (it is demonstrably not the case that `WITH` is parsed as the transaction name). T-SQL parsing is... complicated. – Jeroen Mostert Jul 19 '22 at 15:53
  • fwiw, it's also not deterministic whether this will delete row 3 or 4. It could opt for either row depending on when you run the delete statement. – Joel Coehoorn Jul 19 '22 at 16:07

0 Answers0