Dynamic Insert/update based on where clause in postgresql

Question

I have two tables as shown below

ob_period

person_id     ob_start_date

  1            2007/02/11
  2            2008/05/13  
  3            2008/07/29
  4            2006/03/21

visit

person_id     visit_date

  1            2003/06/21
  2            2005/02/23  
  3            2006/04/19
  5            2004/06/11

I would like to update the "ob_start_date" of "ob_period" table with "visit_date" of visit_table

I was trying something like below for update but it can't work as I am not sure how to update with a dynamic value from another table

update ob_period a
set a.ob_start_date = b.visit_date
where a.person_id = b.person_id

I expect my output to be like as shown below

output

person_id     visit_date

  1            2003/06/21
  2            2005/02/23  
  3            2006/04/19
  4            2006/03/21

Can you help me with this please?

Gordon Linoff · Accepted Answer · 2020-02-21 11:31:17Z

1

You need a table reference to the second table:

update ob_period p
    set ob_start_date = v.visit_date
    from visit v
    where p.person_id = v.person_id

answered Feb 21, 2020 at 11:31

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

@TheGreat . . . Note that I changed the table aliases so they are meaningful abbreviations for the tables, rather than arbitrary letters.

Naveen Kumar · Accepted Answer · 2020-02-21 11:32:07Z

1

Try this:

 update ob_period a
    set a.ob_start_date = b.visit_date
    from visit b
    where a.person_id = b.person_id

answered Feb 21, 2020 at 11:32

Naveen Kumar

1,9961 gold badge9 silver badges11 bronze badges