Re: update without SET ? - Mailing list pgsql-sql
| From | Michael Moore |
|---|---|
| Subject | Re: update without SET ? |
| Date | |
| Msg-id | CACpWLjMp=C9fU2LToKHgjaAe4HdFVkmRyjPse9yGmNFrBjF3JA@mail.gmail.com Whole thread Raw |
| In response to | Re: update without SET ? ("David G. Johnston" <david.g.johnston@gmail.com>) |
| Responses |
Re: update without SET ?
("David G. Johnston" <david.g.johnston@gmail.com>)
|
| 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;
On Wed, Feb 3, 2016 at 2:20 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
I want to UPDATE a table but I will not know until run time which columns will be updated. I would like to do something like this:update mytest t (SELECT * FROM json_populate_record(null::mytest,'{"key":22,"header":44,"ident":66,"static01":"this","static02":"that"}'));In other words, I will be receiving a json document on an input parameter. The columns named in the json document can be a sub set of those that exist in the table.If a key is defined but has a value of null what do you want to do?not fully tested...UPDATE mytestSET col1 = COALESCE(src.col1, mytest.col1)FROM (SELECT * FROM json_populate_record(...)) src;json_populate_record outputs the full structure of mytest regardless of its input so simply match every column and use COALESCE to choose between the value in the json and the value already on the table.Aside from the null json value issue that should work - if not you probably need to describe your problem in more detail.Modification thought to deal with JSON null values on existing keys:UPDATE mytestSET col1 = (CASE WHEN src.json_value ? 'col1' THEN src.col1 ELSE mytest.col1 END)FROM (SELECT *FROM json_populate_record(json_value)CROSS JOINSELECT json_value AS source_json) src WHERE mytest.key = src.key;David J.