Update with multiple inner joins

[TIM_M_91]

Ok so I have three tables Customers,Invoices,and Delivery with the fields:

Customers:
CustomerID
Address
........

Orders:
OrderID
DeliveryID
.......

Delivery:
DeliveryID
DeliveryToAddress
......

So what I want to do is update my delivery table for the DeliveryToAddress for when the customers address changes within the customers tables. To get the Customers address into the delivery table it needs to pass through the order table. How do I do this as everything that I have tried does not seem to work?

[urtrivedi]

UPDATE TABLE_1 LEFT JOIN TABLE_2 ON TABLE_1.COLUMN_1= TABLE_2.COLUMN_2 
SET TABLE_1.COLUMN = EXPR WHERE TABLE_2.COLUMN2 IS NULL

This is sample query refer following link

http://dev.mysql.com/doc/refman/5.0/en/update.html

But I would suggest that you should not update address in delivery,

that will changes all address in delivery table of same customer even if delivery was made to old address. So you will loose history, that where were the consigment delivered in past.

It is good to pickup new address from customer table while taking new order and not to change address of past orders.

[diafol]

Addresses IMO should have their own table. And then you have a 'user_address' link table. That way an user can have as many addresses as they need with a flag for billing/delivery/deleted (don't show). SO the deleted ones still exist for historical purposes, they just don't show in the user's 'choose address' section.