sql / coldfusion删除重复行 [英] sql/coldfusion delete duplicate rows

问题描述

这是我的表格

Display          UPC
0                0553406259120
0                0753406259120
1                0753406259120
1                0453406259120

如果您注意到，row 2和3具有相同的UPC。我想删除所有具有display = 0和重复upc的行。所以在我的表中，我想删除第2行。这里是我的coldfusion代码到目前为止，不工作。请指教。

If you notice, row 2 and 3 have the same UPC. I would like to delete all rows that have display = 0 and duplicate upc. So in my table I want to delete row 2 only. Here is my coldfusion code so far that doesn't work. Please advice.

<cfquery name="GetData" datasource="#Application.ds#" dbtype="ODBC" username="#Application.UserName#" password="#Application.Password#">
DELETE UPC
FROM products
WHERE DISPLAY = 0
GROUP BY UPC  
HAVING COUNT(*)>1
</cfquery>

推荐答案

假设您要删除< c $ c> UPC 是相同的，但显示是不同的：

Assuming that you want to delete all rows where UPC is the same, but Display is different:

DELETE FROM Products as a
WHERE display = 0
AND EXISTS (SELECT '1'
            FROM Products as b
            WHERE b.display <> 0
            AND b.upc = a.upc)

RDBMS，并将删除 UPC 相同，但使用不同显示代码的所有行。

This should work on all RDBMSs, and will remove all rows where UPC is the same, but with a different Display codes.

这篇关于sql / coldfusion删除重复行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！