Re: update without SET ?

Hi David.
This gives ...
ERROR: column src.key​ does not exist
LINE 8: WHERE mt.key = src.key​;
^
HINT: Perhaps you meant to reference the column "src.key".
********** Error **********
Funny how it is suggesting the exact thing that it says is the problem. In
reality, I will have the key value in a variable so I can do ...
WHERE mytest.key = key_variable;

There are a few techniques you are using here that I am not familiar with.
I will study-up and get back to you later.

Mike

On Wed, Feb 3, 2016 at 3:35 PM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Wed, Feb 3, 2016 at 4:09 PM, Michael Moore <michaeljmoore(at)gmail(dot)com>
> wrote:
>
>> 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;
>>
>>
> UPDATE mytest
> SET header = CASE WHEN source_json ? 'header' THEN src.header ELSE
> mytest.header END
> FROM (
> SELECT *
> FROM (VALUES
> ('{"key":22,"header":44,"ident":null,"static02":"that"}'::jsonb)) src_json
> (source_json),
> LATERAL jsonb_populate_record(null::mytest, source_json)
> ) src
> WHERE mytest.key = src.key
> ​;
>
> ​LATERAL makes writing this a bit cleaner​ but is not mandatory. The
> CROSS JOIN would have worked but didn't feel like playing with the syntax.
>
> I am not sure where you got the idea to try " WHEN src.header = 'header'"
> especially since the value of src.header is 44...
> ​
> Apparently the exists operator (no matching function so if you cannot use
> "?" as an operator you will have issues...) is jsonb only.
> ​
>
>