Re: update without SET ?

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
> <javascript:_e(%7B%7D,'cvml','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
>> <javascript:_e(%7B%7D,'cvml','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.
>> ​
>>
>>
>