Multiple updates using select statement and joins in Postgresql

Question

I am very new to postgres,

I am trying to achieve multiple updates by joining tables using json object in posgres.

I have a table "timestamp_snapshot" with below data..

   channel_id(varchar)         date_in (timestamp)
      "sam_channel"        "2016-06-27 19:36:40.706018"
      "vam_channel"        "2016-06-27 19:36:40.706018"

I have a json object as below

'{ "sam_channel" : "2016-06-27T19:36:40.706018", "vam_channel" : "2016-06-29T19:42:34.812616" }'

I'd like to update the date_in column of table "timestamp_snapshot" using the data from the json object

The output, after update, I'd like to have is given below:

 channel_id(varchar)         date_in (timestamp)
  "sam_channel"          "2016-06-27 19:36:40.706018"
  "vam_channel"          "2016-06-29 19:42:34.812616"

I tried the problem with the below approach but the data in the table "timestamp_snapshot" remains the same even after the update.

my approach:

update timestamp_snapshot
set date_in = latest_snap.value
from
    timestamp_snapshot as tab_time_snap,
    (
     select j.key, j.value::timestamp 
     from json_each_text('{ "sam_channel" : "2016-06-27T19:36:40.706018", 
                      "vam_channel" : "2016-06-29T19:42:34.812616" }') j
    ) latest_snap
where latest_snap.key = tab_time_snap.channel_id

I see their is some problem with the query, since I am new to postgres, I'd really appreciate for any help.

Nick · Accepted Answer · 2016-06-29 15:32:54Z

1

It's possible that it is a result of you referencing the table to be updated twice. If you change your statement to this, do you get your intended result?:

UPDATE timestamp_snapshot
SET date_in = latest_snap.value
FROM
    (
     SELECT j.key, j.value::timestamp 
     FROM json_each_text('{ "sam_channel" : "2016-06-27T19:36:40.706018", 
                      "vam_channel" : "2016-06-29T19:42:34.812616" }') j
    ) latest_snap
WHERE timestamp_snapshot.channel_id = latest_snap.key;

Reference to the docs: https://www.postgresql.org/docs/9.5/static/sql-update.html

answered Jun 29, 2016 at 15:32

Nick

7,4912 gold badges25 silver badges46 bronze badges

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

Collectives™ on Stack Overflow

Multiple updates using select statement and joins in Postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related