mysql 关联表修改、新增/单表重复数据删除

最新推荐文章于 2024-11-18 17:31:55 发布

喵-

最新推荐文章于 2024-11-18 17:31:55 发布

阅读量368

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/qq_41231439/article/details/79851221

版权

新增：

INSERT INTO table_name(index1,index2,index3)
SELECT x.index1,x.index2,x.index3
FROM (SELECT index1,index2,index3
FROM table_name_1
WHERE (index1,index2)
not in (SELECT index2,index2 FROM table_name)) x;

修改：

update table_name a inner join(
select index1,index2,index3,index4
) c on
a.index1=c.index1 and a.index2=c.index2 
set a.index3=c.index3,a.index4=c.index4;

修改：UPDATE table_name a,table_name1 b SET a.index1=b.index1 WHERE a.index2 = b.index2

同表重复数据删除：

CREATE TABLE `football_data_team.copy` SELECT
t.com_id,t.com_name,t.com_name_full,t.season,t.team_id,t.team_name,t.team_name_en
FROM
`football_data_team` t
GROUP BY
t.com_id,t.com_name,t.com_name_full,t.season,t.team_id,t.team_name,t.team_name_en;
DROP TABLE `football_data_team`;
ALTER TABLE `football_data_team.copy` RENAME TO `football_data_team`;