Re: 2 Dumb newbee questions

From: Matthias Weiß <matthiasUNDERSCOREweiss_at_gmx.at>
Date: Sun, 18 Jul 2004 22:04:43 +0200
Message-ID: <40fad8a1@e-post.inode.at>

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.....

> 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, 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

??????

Tank you very much for your help.

servus, matthias Received on Sun Jul 18 2004 - 15:04:43 CDT