SQL Server SQL to remove duplicated records in a table

t3psigkw 于 2023-03-07 发布在其他

关注(0)|答案(1)|浏览(135)

I have a table named commercialcopy with 4 columns. I want to remove the duplicated row on the table.

My script is below

DELETE FROM commercialcopy
where (BUSINESSREGISTRATIONNUMBER, BUSINESSNAME, CustomerIDCORP, BUSINESSOFFICEADDRESSLINE1) NOT IN (
SELECT MIN(BUSINESSREGISTRATIONNUMBER), MIN(BUSINESSNAME),MIN(CustomerIDCORP),MIN(BUSINESSOFFICEADDRESSLINE1)
FROM commercialcopy
  GROUP BY BUSINESSREGISTRATIONNUMBER, BUSINESSNAME, CustomerIDCORP, BUSINESSOFFICEADDRESSLINE1
);

ERROR:

I am getting the below redline error on the first column_name argument "where (BUSINESSREGISTRATIONNUMBER,"
An expression of non-boolean type specified in a context where a condition is expected

sql-server

来源：https://stackoverflow.com/questions/75579834/sql-to-remove-duplicated-records-in-a-table

1条答案

按热度按时间

6ss1mwsb1#

The issue with your script is that the WHERE clause expects a boolean expression, but you are comparing multiple columns to a subquery result which is not a valid boolean expression.

You can modify your script to use EXISTS instead of NOT IN, like this:

DELETE FROM commercialcopy c1
WHERE EXISTS (
    SELECT 1
    FROM commercialcopy c2
    WHERE c1.BUSINESSREGISTRATIONNUMBER = c2.BUSINESSREGISTRATIONNUMBER
      AND c1.BUSINESSNAME = c2.BUSINESSNAME
      AND c1.CustomerIDCORP = c2.CustomerIDCORP
      AND c1.BUSINESSOFFICEADDRESSLINE1 = c2.BUSINESSOFFICEADDRESSLINE1
      AND c1.ROWID < c2.ROWID
);

This will delete all rows from commercialcopy except the one with the smallest ROWID for each set of duplicated values in the four columns.

赞(0）回复(0）举报 2023-03-07

我来回答

SQL Server SQL to remove duplicated records in a table

1条答案

相关问题

热门标签

最新问答