mysql update query with inner join

Question

I have two tables customer and order. I want to update different values in both tables with one query. For example customer table has a city column and value is germany and order table has status column and value is hold, I want to change germany to london and hold to resolved with one query. Here is the query below

UPDATE customer,order INNER JOIN order ON customer.cust_id = order.cust_id SET cust_city = 'Lahore' AND order_status= 'Resolved' WHERE cust_id = 2

mysql is showing error for this query

score 12 · Accepted Answer · answered Aug 23 '15 at 13:43

MySQL supports this operation:

UPDATE customer c INNER JOIN
       order o
       ON c.cust_id = o.cust_id
    SET c.cust_city = 'Lahore',
        o.order_status = 'Resolved'
    WHERE c.cust_id = 2 ;

Note: order is a really bad name for a table, because it is a SQL keyword. Choose names for things that do not need to be escaped.

score 0 · Answer 2 · answered Jul 20 '20 at 13:03

It is very simple to update using a join query in SQL.You can even join two or more tables. Here is an example :

    UPDATE customer_table c 

     INNER JOIN  
          employee_table e
          ON c.city_id = e.city_id  
     INNER JOIN 
          anyother_ table a
          ON a.someID = e.someID

    SET c.active = "Yes"

    WHERE c.city = "New york";

mysql update query with inner join

2 Answers2

Linked