Update table with JOIN on another

I had to selectively updat a table in postgres by doing a JOIN on another table. Here’s an example of the schema and data that I needed to push:

table_system
id | installation_id | region | appliance_role

table_tenant
id | installation_id | host_controller

UPDATE table_system s
SET  region = 'US-West'
FROM table_tenant t
WHERE trim(t.host_controller) IN ('server-10.domain.com', 'server-11.domain.com')
AND t.installation_id = s.installation_id   -- USED For the JOIN
AND s.appliance_role='H';
Advertisements

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