Turpan Turpan - 11 months ago 68
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 Source

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.