Re: update without SET ?

using 9.5
Also, tried "src.key" and that does not work either, and tried about a
dozen permutation none of which worked. src.src.key gave some interesting
results if I recall.
Also, was just thinking that two columns with names of say: attrib and att
would cause a problem if att was NOT in the JSON and attrib was in,
because: 'att' ? 'attrib' = true. I should be able to clean this up if
indeed it is a problem at all. Have not tested it yet.

Thanks for all your help David, not only are you solving my problem, I'm
learning a lot.

Mike

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

> What version? I am reasonably certain it ran as copied on 9.5 but maybe a
> pasto.
>
> Btw: src.key is not the same as "src.key" - the former is column key on
> relation src while the later is the in-scope column named "src.key"
>
> David J.
>
> On Wednesday, February 3, 2016, Michael Moore <michaeljmoore(at)gmail(dot)com>
> wrote:
>
>> 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.
>>> ​
>>>
>>>
>>