Is this your requirement - if there are 3 rows in a table having the same data in all the columns then you need
to mark only 2 of them as duplicates and one remains unchanged or you want to mark all 3 rows as
if possible please provide a sample example
As commented by rajeev, sample would provide more clarity on your requirement.
on a brief note:
You can use a self left join to table with left joining table to be a derived table on main table with duplicated PI (if you are checking for complete duplicated row) and you can use case statements to compare the columns to mark the duplicate flag.
following sets the duplicate flag for all duplicate rows
SET dupflag = 1
WHERE (col1,col2,col3) IN
GROUP BY 1,2,3
HAVING COUNT(*) > 1
CASE WHEN COUNT(*) OVER (PARTITION BY col1,col2,col3) > 1 THEN 1 ELSE 0 END
What should be the UPDATE statement in case of below requirement.
Requirement - if there are 2 perfect duplicate rows in a table having the same data in all the columns then i need to update column1 of one row only.
You can use row_number to assign different numbers to each perfectly duplicate rows and then update 1 columns of 1 row by putting filter condition like row_number = 1