Re: update without SET ? - Mailing list pgsql-sql

Lost me here David. I can't seem to bridge the gap between your pseudo-code and an actual example.

setup:

CREATE TABLE mytest

(key bigint NOT NULL,

  header bigint NOT NULL,

  ident bigint NOT NULL,

  static01 character varying(100),

  static02 character varying(220) );

INSERT into mytest

  SELECT * FROM json_populate_record(null::mytest,'{"key":22,"header":44,"ident":66,"static01":"this","static02":"that"}');

And here is my failed interpretation of your example: (I used inline JSON string just for ease. In real life it will be a variable of type JSON.)

UPDATE mytest

     SET header = (CASE WHEN src.header = 'header' THEN src.header ELSE mytest.header END)

FROM ( 

       SELECT * FROM json_populate_record(null::mytest, '{"key":22,"header":44,"ident":null,"static02":"that"}') 

       CROSS JOIN

      (SELECT '{"key":22,"header":44,"ident":null,"static02":"that"}') AS source_json 

     ) src 

WHERE mytest.key = src.key;