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)
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'f'.
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