update複数テーブルmysql

update条件にjoinが必要、あるいは、複数テーブルを更新するときに使うね、あまりないと思う

mysql> create database kgtest;
Query OK, 1 row affected (0.03 sec)
mysql> use kgtest
Database changed
mysql> create table students (`id` int(11) not null, `name` varchar(10) not null);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into students value(1,'tom');
Query OK, 1 row affected (0.03 sec)
mysql> insert into students value(2,'Jerry');
Query OK, 1 row affected (0.00 sec)
mysql> select * from students;
+----+-------+
| id | name |
+----+-------+
| 1 | tom |
| 2 | Jerry |
+----+-------+
2 rows in set (0.00 sec)
mysql> create table student2 (`id` int(11) not null, `name` varchar(10) not null);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into student2 value(1,'tomas');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student2 value(2,'rose');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student2;
+----+-------+
| id | name |
+----+-------+
| 1 | tomas |
| 2 | rose |
+----+-------+
2 rows in set (0.00 sec)
mysql> update students st1,student2 st2 set st1.name = st2.name,st2.name = 'kitarou' where st1.id = st2.id;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> select * from student2;
+----+---------+
| id | name |
+----+---------+
| 1 | kitarou |
| 2 | kitarou |
+----+---------+
2 rows in set (0.00 sec)
mysql> select * from students;
+----+-------+
| id | name |
+----+-------+
| 1 | tomas |
| 2 | rose |
+----+-------+
2 rows in set (0.00 sec)