How to MySQL update join with other table

MySQL Update Join

Sometimes we need to update a MYSQL table data based on other table data.

For example order_table field city is NULL, and users_data have a field city, we need to update order_table city from users_table city.

There are many ways to do this, but this query is the simplest one. This sample is for beginner users.

Here is working MySQL query to update MySQL table data from other table.

UPDATE 
    order_table LEFT JOIN users_table ON order_table.user_id=users_table.user_id
SET 
    order_table.city = user_table.city,
    order_table.phone_number = user_table.phone_number
WHERE 
    order_table.city IS NULL OR order_table.phone_number IS NULL;

order_table field user_id and users_table field user_id must be indexed for performance.

ServerDiary

ServerDiary

One thought on “How to MySQL update join with other table

Leave a Reply

Your email address will not be published. Required fields are marked *