mysql development skills: JOIN update and data duplication checking/deduplication_MySQL

Mainly involves: JOIN, JOIN update, GROUP BY HAVING data duplication checking/deduplication

1 INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN (not supported by MySQL), CROSS JOIN

This is a very good blog post I found on the Internet, illustrating the join statement:

CODING HORROR-A Visual Explanation of SQL Joins

The picture below can clearly understand the data selection range of join

[][1]
[1]: http://7xs09x.com1.z0.glb.clouddn.com/160725-imooc-mysql-development-skills-notes-001.png

2 Update the table that includes itself in the use filter

Update the repeated fields of col_a in the t1 t2 table

UPDATE t1 
SET col_a = 'hi' 
WHERE t1.col_a IN (
 SELECT b.col_a
 FROM t1 a INNER JOIN t2 b on
 a.col_a = b.col_a
)
;
ERROR:1093 

can be converted to:

UPDATE t1 aa JOIN(
 SELECT b.col_a
 FROM t1 a INNER JOIN t2 b on
 a.col_a = b.col_a
)bb on aa.col_a= bb.col_a
SET col_a = 'hi' 
;

3 Query and delete duplicate data

Use GROUP BY and HAVING to query duplicate data

SELECT col_a, COUNT(*)
FROM t1
GROUP BY col_a HAVING COUNT(*) > 1
;

Delete duplicate data and keep the one with the largest ID for the same data

DELETE a
FROM t1 a JOIN (
 SELECT col_a,COUNT(*),MAX(id) AS id
 FROM t1
 GROUP BY col_a HAVING COUNT(*) > 1
)b ON a.col_a = b.col_a
WHERE a.id < b.id
;

Thanks for reading this article, I hope it can help everyone, thank you for your support of this site!