| Author |
 Topic  |
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
United Kingdom
22431 Posts |
 Posted - 05/11/2007 : 14:47:30
|
| WE WANT MORE CURSORS .... |
 |
|
|
pootle_flump
Flowing Fount of Yak Knowledge
United Kingdom
1064 Posts |
Posted - 05/14/2007 : 08:41:17
|
quote:
Originally posted by Kristen
I've absoBloodyLutely seen it all now!!!!
For a split second I thought the same - until the next time "I've absoBloodyLutely seen it all now!!!!". |
|
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 05/14/2007 : 08:53:23
|
Yeh, but you see, you all posted several replies going on about the importance of joins and all getting worked up.
I'd have just did what Pete did, and carried on smoking my Hamlet.
Drew
"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK." |
Edited by - drewsalem on 05/14/2007 08:56:11 |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7020 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30319 Posts |
Posted - 05/14/2007 : 10:56:48
|
Is it just me, or have posts [to our suggestions] containing follow-up answers like "Does it work?" increased?
Don't they dare to test anymore? Are all in live productions?
Peter Larsson
Helsingborg, Sweden |
|
|
|
DonAtWork
Flowing Fount of Yak Knowledge
2166 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7020 Posts |
Posted - 05/14/2007 : 11:18:06
|
quote:
Originally posted by X002548
Well, I get Access denied, but that's understandable about his statement..
DB2 Doesn't allow it....
Can you restate what he's saying?
Brett
8-)
Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Add yourself!
http://www.frappr.com/sqlteam
I believe his point is that the INNER JOIN syntax will work OK but may produce incorrect results if the join matches multiple rows, while the ANSI form of the UPDATE will fail when the sub-query returns multiple rows, and let the developer know that something is wrong.
From Celko's post:
"...
UPDATE Orders
SET Orders.SomeCol = OrderDetails.item_price
FROM Orders
INNER JOIN
OrderDetails
ON Orders.order_nbr = OrderDetails.order_nbr;
The flakey update above is probably either the result of incorrect query writing, or of an improperly specified request to "Set some_col in the Orders table equal to the prices of the OrderDetails associated with the order". You and I both know that this request is not specific enough and has to be amended, but apparently, the junior programmer who wrote this did not know that. His tests all resulted (by coincident) in the expected results, so the query goes into production and can wreak havoc to the data for several days before an angry customer calls.
This would not have happened with the ANSI syntax. That's the point that I am trying to make. The ANSI equivalent of the incorrect query above is
UPDATE Orders -- no alias allowed!
SET some_col
= (SELECT item_price
FROM OrderDetails
WHERE OrderDetails.order_nbr = Orders.order_nbr)
WHERE EXISTS
(SELECT *
FROM OrderDetails
WHERE OrderDetails.order_nbr = Orders.order_nbr);
This will of course result in an error, and even the most junior of junior programmers will eventually figure out (probably by asking a senior) what's wrong. At that point, either the query is corrected to match the request, or a note is sent back to management asking for a clarification of the ambiguity in the request.
Bottom line: UPDATE FROM *can* be safely used - but only if you are FOREVER certain that no single row in the target table can EVER be joined to more than one row in the source table(s); FOREVER in the ENTIRE LIFETIME of the application; FOREVER across all programmers yet to come.
I like to err on the safe side, I do not bet only an endless stream of 100% perfect programmers.
..."
CODO ERGO SUM |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
|
|
jezemine
Flowing Fount of Yak Knowledge
USA
2886 Posts |
Posted - 05/14/2007 : 18:13:53
|
graz should change his title to "join hater"
www.elsasoft.org |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
17684 Posts |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
Australia
4970 Posts |
Posted - 05/15/2007 : 00:01:26
|
quote:
Originally posted by jezemine
graz should change his title to "join hater"
www.elsasoft.org
*Looks around*
*Whistles*
Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson |
|
|
|
jezemine
Flowing Fount of Yak Knowledge
USA
2886 Posts |
|
|
khtan
In (Som, Ni, Yak)
Singapore
17684 Posts |
Posted - 05/15/2007 : 04:43:43
|
quote:
Originally posted by Merkin
quote:
Originally posted by jezemine
graz should change his title to "join hater"
www.elsasoft.org
*Looks around*
*Whistles*
Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson
"I hate cross join"
Is that how Jeff got his custom title as well ? 
KH
|
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
mcrowley
Aged Yak Warrior
771 Posts |
|
|
Topic |
|
Twit List
