Postgresql update 2 tables in one update statement

Question

I have two different tabs with same field, like:

host.enabled, service.enabled.

How I can update his from 1 update statement?

I tried:

UPDATE hosts AS h, services AS s SET h.enabled=0, s.enabled=0
    WHERE
    ctid IN (
    SELECT hst.ctid FROM hosts hst LEFT JOIN services srv ON hst.host_id = srv.host_id
        WHERE hst.instance_id=1
    );

On mysql syntax this query worked like this:

UPDATE hosts LEFT JOIN services ON hosts.host_id=services.host_id SET hosts.enabled=0, services.enabled=0 WHERE hosts.instance_id=1

Answer 1

I didn't really understand your schema. If you can set up a fiddle that would be great.

In general though to update two tables in a single query the options are:

Trigger

This makes the assumption that you always want to update both together.

Stored procedure/function

So you'll be doing it as multiple queries in the function, but it can be triggered by a single SQL statement from the client.

Postgres CTE extension

Postgres permits common table expressions (with clauses) to utilise data manipulation.

with changed_hosts as (
  update hosts set enabled = true 
  where host_id = 2 
  returning * 
)
update services set enabled = true 
where host_id in (select host_id from changed_hosts);

In this case the update in the WITH statement runs and sets the flag on the hosts table, then the main update runs, which updates the records in the services table.

SQL Fiddle for this at http://sqlfiddle.com/#!15/fa4d3/1

In general though, its probably easiest and most readable just to do 2 updates wrapped in a transaction.