POSTGRESQL – complex updates

Fairly simple example: your file structure is as follows:

table1(parent_id)-> table2 (id)

table2(parent_id)-> table3(id).

table 1 links to table2 and table2 links to table3

For whatever reason table3 has to go. But  you want to update table1 with table3’s id.

so you’ve created a new column table3_id. (I know very bad naming convention but I’m hoping it aids the understanding of the solution)

update file1 set table3_id =

(select table3.id

from table1

inner join table2 on (table2.id = table1.parent_id  )

inner join table3 on ( table3.id = table2.parent_id ) )

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