I agree! I have been coding and administrating for a number of years have
taken many Oracle classes, and when I first saw Thomas' SQL I thought that
it must have been a mistake.
Gene Hubert <ghubert_at_netpath.net> wrote in message
news:177EC3699B1A3DA2.78FED0CBFF0E9B3F.585D5730F40FB5C2_at_lp.airnews.net...
> On Tue, 31 Aug 1999 18:06:57 GMT, tkyte_at_us.oracle.com (Thomas Kyte)
> wrote:
>
> >A copy of this was sent to ghubert_at_netpath.net (Gene Hubert)
> >(if that email address didn't require changing)
> >On Tue, 31 Aug 1999 03:23:52 GMT, you wrote:
> >
> >>On Mon, 30 Aug 1999 16:33:58 GMT, tkyte_at_us.oracle.com (Thomas Kyte)
> >>wrote:
> >>
> >>Well, that's a new one on me. Very nifty trick there Thomas. Much
> >>cleaner than adding an exists clause.
> >>
> >>I'm really glad to pick up a handy technique like this but I have to
> >>ask: How is anybody supposed to deduce something really useful like
> >>this from the documentation provided for the update statement? I
> >
> >from the 7.3 manual I deduced it quite easily actually.....
>
> discussing the following example:
> -----------------------------
> tkyte_at_8.0> update
> 2 ( select table1.columna t1a, table2.columna t2a
> 3 from table1, table2
> 4 where table1.columnb = table2.columnb )
> 5 set t1a = t2a
> 6 /
> ---------------------------
>
> Well yes, it's certainly possible to deduce from the docs. I think
> you'd agree it's less obvious in the 8.1.5 docs where several layers
> of complexity have been added to the update statement. I was just
> looking at the pdf version. Can you see someone who is relatively new
> to Oracle going to the 8.1.5 docs and figuring this out? I can't.
>
> I've been wanting to write sql statements like that for 3 years (since
> I started working with Oracle). I used it twice today at work and was
> very pleased to find I can even update clob columns that way.
>
> I read this group a lot and have studied the books from the official
> Oracle DBA and PL/SQL classes and your example is the first I've seen
> of doing a correlated update in this way. I've used subqueries in
> select statements a lot but did not know it was possible in updates
> and deletes. To be fair I must add that I've always been hired as
> someone who could come in and get a project or department that was new
> to Oracle up and running so I've never had a base of Oracle developers
> or code to learn from at work.
>
> I was writing this kind of sql with SQL Server withing a few weeks of
> starting to work with the product. I went to the help and quickly
> found a specific example of how to do such a correlated update. In
> spite of the other weaknesses of that product, I think Microsoft often
> has a better grasp of which features to emphasize in their help and
> user interface to make their products seem easy to use.
>
> You've provided an excellent example. I just think it should be in
> the official help. This kind of correlated update is a real "bread
> and butter" query that I could have used to write much more intuitive
> and easy to understand sql the past few years. I could have also
> enhanced the perception of several other developers that Oracle was a
> more intuitive and easier to use product.
>
> I would be totally intrigued to get a feel for what percentage of
> Oracle developers and dba's who are reading this know about and use
> the above correlated update technique. Anybody want to respond and
> let the world know in this official internet survey?
>
> Gene Hubert
> SkillsPoint.com
>
Received on Tue Aug 31 1999 - 23:33:09 CDT
