Update database table depending on result from SELECT with CASE clause

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.

Advertisements

About admin

Just another php developer trying to give something back to the community.
This entry was posted in Best practices, SQL and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s