Re: 2 Dumb newbee questions

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 19 Jul 2004 09:52:08 -0500
Message-ID: <u658kcar0.fsf@standardandpoors.com>

On Sun, 18 Jul 2004, matthiasUNDERSCOREweiss_at_gmx.at wrote:
> Galen Boyer wrote:
>
>

>> So the table will get created without any data in it.
>> Structure only.  1 never equals 2 (except if it is late and
>> you started drinking early, then 1 = 2 = "A TEN"!)

>
> Thanx for the answer, I was getting mad about it. I thougt it
> maybe numberes the tables and compares table 1 with table 2 or
> column 1 with column 2 .... or some other magic. Maybe I'm
> just too focused to think complicated.....

Glad it helped.

>> col_3 and col_5 maps to one of either foo or bar.  But, the
>> col_1 and col_2 that are being set clearly map to bar, so bar
>> is being updated.  What it is updated from isn't clear, col_3
>> and col_5 could be from either table.  I would assume at least
>> one of them is from foo, or why make the join and why use the
>> update syntax in the first place.
>> 
>>> The inner select statement returns 5 columns, but only 2 get
>>> updated, so what's the point of this select?
>> 
>> Let me rewrite how I usually author these, so then, maybe it
>> might make more sense.  (Note, col_4 is unneeded so I removed
>> it)
>> 
>>      update
>>      (
>>        select
>>          ck.col_1  old_col_1,
>>          ck.col_2  old_col_2,
>>          col_3     new_col_1,
>>          col_5     new_col_2
>>        from foo i, bar ck
>>        where i.col_1     = ck.col_1
>>          and i.col_2     = ck.col_2
>>      )
>>      set  old_col_1 = new_col_1,
>>           old_col_2 = new_col_2
>>     ;
>> 
>> Does this help?

>
> Yes this helps, I think I get the point though I know this
> syntax only from create table statements where an existing
> table is used to specify the columns. What I still don't
> understand, does it make any sense to use this select statement
> when it's not used in the set clause? Ok, it limits the rows
> to be updated to where i.col_1 = ck.col_1 and i.col_2 =
> ck.col_2 it also provides the reference to the other table
> being used,

This is really your answer.

> but why select any columns ck.col_1 old_col_1, ck.col_2
> old_col_2, col_3 new_col_1, col_5 new_col_2

Let me turn it around on you. How would you describe what the update should look like?

-- 
Galen Boyer

Received on Mon Jul 19 2004 - 09:52:08 CDT