| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: update, select and join in one statement ?
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:
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 - 21:30:28 CDT
 |
 |