Mysql development skills-JOIN update and data re-checking/de-duplicating; development skills-join

Mysql development skills-JOIN update and data re-checking/de-duplicating; development skills-join

Mainly involved: JOIN, JOIN update, group by having data re-query/de-duplication

1 inner join, left join, right join, full join (not supported by MySQL), CROSS JOIN

This is a very good blog post on the Internet. The join statement is illustrated as follows:

CODING HORROR-A Visual Explanation of SQL Joins

We can clearly understand the range of join data selection.

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

2. Update the tables included in the filtering conditions.

Update repeated fields of col_a in Table t1 and t2

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 

Convertible:

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_aSET col_a = 'hi' ;

3. query and delete duplicate data

Query duplicate data using group by and HAVING

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

Delete duplicate data.

DELETE aFROM 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_aWHERE a.id < b.id;

Thank you for reading this article and hope to help you. Thank you for your support for this site!