Re: 2 Dumb newbee questions

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 16 Jul 2004 15:58:04 -0500
Message-ID: <uoemfoemz.fsf@standardandpoors.com>

On Fri, 16 Jul 2004, matthiasUNDERSCOREweiss_at_gmx.at wrote:
> Hallo!
>
> I know a little about SQL but I stumbled over code on an oracle
> server which I don't understand:
>
> create table foo as
> select no, um
> from bar
> where 1=2
>
> As far as I get it, this creates the new table foo with the
> colums no and um which have the same types as the colums with
> the same name in the table bar. But what is this "where 1=2"
> ???

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"!)

> And one last:
>
> update
> (
> select
> ck.col_1,
> ck.col_2,
> col_3,
> ck.col_4,
> col_5
> from foo i, bar ck
> where i.col_1 = ck.col_1
> and i.col_2 = ck.col_2
> )
> set col_1 = col_3,
> col_2 = col_5
>
>
> How do I see in this construct which table is updated?

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?

-- 
Galen Boyer

Received on Fri Jul 16 2004 - 15:58:04 CDT