CODEWITHSUNDEEP
sqldatabasepostgresqlsql-updateforeign-keys
Vincent Tang
Vincent Tang

Reputation: 4170

SQL - Update multiple tables, the same column across one query

I have a postgres database

There are 3 tables, I'm trying to make a sql UPDATE statement that updates an active column in each table.

Table1 has a field record that looks like this:

table1_id: 40b77068-4693-4593-8ea9-996501414101
table1_name: Digital Only
active: false

Table2

table2_id: [some randomId we don't care about]
table1_fk: 40b77068-4693-4593-8ea9-996501414101
active: false

Table3 is the same as table 2

table2_id: [some randomId we don't care about]
table1_fk: 40b77068-4693-4593-8ea9-996501414101
active: false

I need a query that looks for the name "Digital Only" and it's ID on table1. Update it's active column. Update corresponding active columns in the 2 other tables matching the original ID in table1

The tables have pretty long names so ideally I want to alias them:

So far I have something along the lines of this in pseudocode

UPDATE a.active, b.active, c.active
INNER JOIN
FROM table1 a, table2 b, table3 c
SET a.active=true, b.active=true, c.active=true
WHERE a.active=true, b.active=true, c.active=true

Not sure how to do this. The table relationships look like this: enter image description here

Upvotes: 1

Views: 1442

Answers (2)

xzilla
xzilla

Reputation: 1161

A single update statement can only update 1 table at a time, so there are three ways to do this:

  1. have your application look up the information and then wrap 3 updates in a single transaction
  2. use a stored procedure to implement the lookup/update logic (then your app only needs to make 1 call)
  3. play some SQL tricks by wrapping 3 update statements into a common table expression. this is the most “clever” way to do it.

Upvotes: 1

GMB
GMB

Reputation: 222442

I think this does what you want. The idea is first update table1, and to use the returning clause to return the table1_id, that can be used to update the two other tables:

with 
    t1 as (
        update table1
        set active = true
        where table1_name = 'Digital Only'
        returning table1_id
    ),
    t2 as (
        update table2
        set active = true
        from t1
        where table1_fk = t1.table1_id
    )
update table3
set active = true
from t1
where table1_fk = t1.table1_id

Upvotes: 1

Related Questions