Turpan Turpan - 1 month ago 8
SQL Question

wrong syntax while deleting duplicates

I'm trying to delete duplicates using below code:

delete [TABLE] f
where id not in (select min(id)
from [TABLE] s
where f.companyid = s.companyid
and f.value = s.value)


However, it gives me below error message:


Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'f'.


What is wrong with the syntax?

Answer

In SQL Server, I like to use CTEs and row_number() for this purpose:

with todelete as (
      select t.*,
             row_number() over (partition by companyid, value order by id) as seqnum
      from t
     )
delete todelete
    where seqnum > 1;

But the problem with your syntax is that it should start:

delete f from [TABLE] f
    where . . .

In other words, table aliases are defined in the FROM clause.