Description:
How to update a database table depending on a query on another or several tables ?
Solution:
If you want to make a script that does this, it’s very easy, just select all the values you need, and then in code, iterate through all of them, and update as necessary.
If you want to do it from SQL query it’s a bit more difficult, but is very possible, somehow like this:
UPDATE customers SET country =
(CASE WHEN ((SELECT or.order_country FROM orders AS or
JOIN countries AS co ON co.country_id= or.address_country
WHERE or.is_default_country = 1) = ‘internal’) THEN 1
ELSE 2 END)
WHERE is_shipped = 1;
The query updates the country row in the customers table with date depending on the result of the internal query.