Henrique M. Henrique M. - 4 years ago 133
MySQL Question

Mysql - update column from another table data without reducing rows

I got stucked at the following scenario:

table Orders
order_id, order_date, order_customerid, order_state, order_city

table Customers
customer_id, customer_city, customer_state

I need to duplicate customer_city and customer_state into order_city and order_state, which is currently null. I tried using the following join:

select *
from orders o
inner join customers c
on o.customerid = c.id

and then using an update query. But it appears to be grouping orders by customer, so then it does not reflect the actual number of orders, causing some order_city and order_state to be left empty.

What can I do to update EVERY order inside the order table, knowing that there are many orders from the same customer?

Answer Source

You can use multi-table update syntax.

Something like this would work for you:

update orders 
  inner join customers on customers.customer_id = orders.order_customerid 
set orders.order_state = customers.order_state, 
  orders.order_city = customers.order_city
where orders.order_state is null 
  or orders.order_city is null
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download