0

I have two tables in Postgres

TableA
id, item_id, parent_id
1     i1        null
2     i2         1 -> match the id of the first record (1)
3     i3         2 -> match the id of the second record (2)

TableB
parent_id, item_id
  null           i1
   i1            i2         
   i2            i3

i1 is the top level, i2 is the second level and i3 is the third level.

I need to update the parent_id column in table A to have 1 and 2 based on the table B.

I have

Update TableA set parent_id = ? (SELECT id from TableA WHERE TableA.item_id = TableB.parent_id)
from TableB
where TableB.parent_id = TableA.item_id

The above is basically what I need but I am not sure the exact systax to do it. Can anyone help?

Thanks a lot!

Jwqq
  • 997
  • 4
  • 14
  • 22
  • Try this answer. You can do what your asking using an UPDATE FROM statement with a JOIN. https://stackoverflow.com/questions/7869592/how-to-do-an-update-join-in-postgresql – user7396598 Jan 25 '18 at 18:02
  • Thanks, but I don't want to update parent_id based on TableB record. I need to find the record in TableB and use it to update parent_id in TableA. Notice parent_id in TableA is null, 1 , 2 not i1, i2. – Jwqq Jan 25 '18 at 18:06
  • You just need to tweak the values in the answer linked. The syntax is available there. I also see a new answer to the question that seems appropriate. – user7396598 Jan 25 '18 at 18:10
  • Why is `parent_id` a string in one table and an integer in the other? The naming makes it hard for me to understand the question. You appear to want the "depth" in the first table, but I'm not 100% sure. – Gordon Linoff Jan 25 '18 at 18:16

1 Answers1

1

What I think (your not very clear) you want is something like this(not tested):

Update TableA set parent_id = TableA_parent.id
from TableB
inner join TableA TableA_parent
  on TableB.parent_id = TableA_parent.item_id
where TableB.item_id = TableA.item_id
bruno.almeida
  • 2,746
  • 1
  • 24
  • 32