Update multiple MySQL tables in one update statement

Posted by richard on April 12, 2012

To update multiple tables in MySQL using only one update statement is possible and simple to do. The MySQL documentation mentions this.

An example. You have to update the details of a person who has moved house.
In the database the name and telephone number are stored in the `users` table and the address is stored in the `users_addresses` table and `users_addresses`.`user` references `users`.`id`.

You can update the telephone number and address of user 133232322 in one update statement as follows.

UPDATE users, users_addresses SET users.telephone = '01234 567890', 
    users_addresses.add1 = '1 New Road', users_addresses.add2 = 'Newtown'
    WHERE users.id = users_addresses.user AND users.id = 133232322;