嘿,所以我有一组结构为的数据
id product_number product_type
1 1001 car
2 1002 house
但数据有一些重复,其中:
id product_number product_type
1 1001 car
2 1001 house
我需要删除重复项,但只删除=房子的值。
在我看来,查询应该是这样的:
DELETE *
FROM table
WHERE product_number is duplicate AND product_type = house
感谢
在MySQL中,您可以使用join
:执行您想要的操作
delete t
from table t join
(select product, count(*) as cnt
from table
group by product
) tt
on tt.product = t.product
where tt.cnt > 1 and t.product_type = 'house';
您有多种方法:
1.您可以使用以下查询:
INSERT INTO new_table (SELECT DISTINCT * FROM old_table WHERE product_type = house)
2.您可以更改您的表格并更改产品编号列,以便为其添加索引
ALTER IGNORE TABLE jobs
ADD UNIQUE INDEX idx_name (product_number);
因此,具有相同产品编号的重复行将自动删除,有关更多信息,请参阅此处的链接
3.您可以尝试此查询:
DELETE n1 FROM table_product p1, table_product p2 WHERE p1.product_number = p2.product_number AND p1.product_type= house
您可以将UPDATE查询与以下联接一起使用:
delete t1.*
from
t t1 INNER JOIN t t2
ON t1.product_number = t2.product_number
AND t1.product_type='house' AND (
t2.product_type<>'house'
OR t1.id>t2.id
)
DELETE *
FROM table
WHERE id not in
(select max(id) from table
group by product_number)
AND product_type=房屋