mysql跨表更新示例

一、在同一个表中冗余存储记录之间的关系（组织机构树），查询时需要根据冗余字段进行关联查询

例如，下面的示例，用户表中有个字段friend标记其朋友关系，要求找出id=2及他的朋友（父节点）

mysql> select * from user;
+----+-------+--------+
| id | name  | friend |
+----+-------+--------+
|  1 | duan  |      0 |
|  2 | liang |      1 |
|  3 | hou   |   NULL |
+----+-------+--------+
3 rows in set (0.00 sec)

mysql>

sql：用or或union

mysql> SELECT * FROM USER u WHERE u.id=2 OR u.id=(SELECT friend FROM USER WHERE id=2);
+----+-------+--------+
| id | name  | friend |
+----+-------+--------+
|  1 | duan  |      0 |
|  2 | liang |      1 |
+----+-------+--------+
2 rows in set (0.00 sec)

mysql> select * from user t where id=2 union select * from user where id in(select friend from user where id=2);
+----+-------+--------+
| id | name  | friend |
+----+-------+--------+
|  2 | liang |      1 |
|  1 | duan  |      0 |
+----+-------+--------+
2 rows in set (0.00 sec)

mysql>

 

二、根据另一个表条件，更新本表

2、用户表和设备表

mysql> select * from user1;
+----+-------+--------+---------+
| id | name  | device | company |
+----+-------+--------+---------+
|  1 | duan  | NULL   | c1      |
|  2 | liang | NULL   | c2      |
|  3 | hou   | NULL   | c3      |
+----+-------+--------+---------+
3 rows in set (0.00 sec)

mysql> select * from device;
+------+---------+---------+
| d_id | d_name  | company |
+------+---------+---------+
|   11 | shebei1 | c1      |
|   12 | shebei2 | c2      |
|   13 | shebei3 | c3      |
+------+---------+---------+
3 rows in set (0.00 sec)

mysql>

将拥有相同的公司的设备id填入到用户表中

方法：

1. 关联
2. set select from（同一个表内不行报错You can't specify target table 'user2' for update in FROM clause，SQL SERVER 可以）见《mysql update一张表不能直接使用set select from的结果》

mysql> UPDATE user1 u SET device=(SELECT d_id FROM device d WHERE u.company=d.company);
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> UPDATE user1,device SET user1.device=device.d_id WHERE user1.company=device.company;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql>