mysql 删除重复的记录、A表字段更新到B表

最新推荐文章于 2024-12-05 08:44:25 发布

kenhins

最新推荐文章于 2024-12-05 08:44:25 发布

阅读量731

点赞数

CC 4.0 BY-SA版权

分类专栏： SQL mysql 文章标签： mysql

本文链接：https://blog.youkuaiyun.com/kenhins/article/details/78542158

mysql 同时被 2 个专栏收录

35 篇文章

订阅专栏

SQL

33 篇文章

订阅专栏

本文介绍如何使用SQL进行批量数据更新、插入及删除操作。主要内容包括：通过ID关联更新A表字段为B表对应值；将A表记录插入B表，前提是B表中不存在这些记录；删除B表中重复记录，仅保留每组中ID最小的一条记录。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >


 -- A表字段更新为B表的字段值，条件A\B表通过id关联的记录
update b_table as b set b.`name` = (select a.name FROM a_table as a where b.id=a.id) 
 where EXISTS(select * from a_table as c where c.id=b.id); 

-- A表记录insert到B表，条件是B表不存在A表的记录

insert into b_table(id,name) SELECT id,name from a_table as a
where not EXISTS(select * from b_table as b where b.id=a.id); 

-- 删除重复的记录，只保留一条(id最小的记录)

delete from b_table where `name` in(
    SELECT t1.name from(
        SELECT bc.name from b_table as bc where 1=1 GROUP BY bc.name HAVING count(*)>1
    ) as t1
) AND id NOT IN (
    SELECT t2.rowid from(
        SELECT min(c.id) as rowid,c.name FROM b_table as c GROUP BY c.name HAVING count(*) > 1) as t2
);

-- SELECT bc.`name`,count(id) from b_table as bc where 1=1 GROUP BY bc.`name` HAVING count(*)>1;