How can I implement MySQL multi-Table update? Many new users who are new to MySQL databases will have such questions. The following describes the MySQL multi-Table update method for your reference.
MySQL multi-Table update:
- update contact c, contactdroit cd
- set c.user_name = '$username', c.nom = '$lastname', c.prenom = '$firstname', c.passcode = '$password', cd.droit_id = '$droitid'
- where c.contact_id = '$id' and c.contact_id = cd.contact_id;
Example:
- mysql> create table one(id int(10), name varchar(20));
- Query OK, 0 rows affected (0.03 sec)
-
- mysql> create table two(id int(10), name varchar(20));
- Query OK, 0 rows affected (0.05 sec)
-
- mysql> insert one value(1, '1');
- Query OK, 1 row affected (0.00 sec)
-
- mysql> insert two value(22, '22');
- Query OK, 1 row affected (1.02 sec)
-
- mysql> update one o, two t set o.name='oo', t.name='tt';
- Query OK, 2 rows affected (0.00 sec)
- Rows matched: 2 Changed: 2 Warnings: 0
-
- mysql> select * from one;
- +------+------+
- | id | name |
- +------+------+
- | 1 | oo |
- +------+------+
- 1 row in set (0.00 sec)
-
- mysql> select * from two;
- +------+------+
- | id | name |
- +------+------+
- | 22 | tt |
- +------+------+
- 1 row in set (0.00 sec)
-
Mysql multi-Table query implementation
Mysql temporary table usage
MySQL Stored Procedure for table splitting
In-depth discussion on MySQL Lock Mechanism
Implementation of MySQL fuzzy query with multiple fields in a single table