SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 The Yak Corral
 Twit List
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 88

X002548
Not Just a Number

15586 Posts

Posted - 05/11/2007 :  14:33:54  Show Profile  Reply with Quote
quote:
Originally posted by Kristen

I've absoBloodyLutely seen it all now!!!!

"The code you posted probably works just fine but I hate using INNER joins so give me another method"

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=83479

Kristen



Hey I gave him the answer he wants..I wonder why he is so sad



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



Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 05/11/2007 :  14:47:30  Show Profile  Reply with Quote
WE WANT MORE CURSORS ....
Go to Top of Page

pootle_flump
Flowing Fount of Yak Knowledge

United Kingdom
1064 Posts

Posted - 05/14/2007 :  08:41:17  Show Profile  Reply with Quote
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!!!!".
Go to Top of Page

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 05/14/2007 :  08:53:23  Show Profile  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 05/14/2007 :  09:24:52  Show Profile  Reply with Quote
How do you mix 2112? Or Hemisphere's?



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



Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 05/14/2007 :  10:17:33  Show Profile  Reply with Quote
quote:
Originally posted by Kristen

I've absoBloodyLutely seen it all now!!!!

"The code you posted probably works just fine but I hate using INNER joins so give me another method"

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=83479

Kristen



I found this thread on SQL Server Central where Joe Celko says not to use the SQL Server UPDATE FROM...INNER JOIN syntax for Update statements.
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=169&messageid=362818#bm362848

Maybe sadbjp is a Joe Celko disciple.




CODO ERGO SUM
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 05/14/2007 :  10:43:30  Show Profile  Reply with Quote
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



Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 05/14/2007 :  10:56:48  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 05/14/2007 :  11:03:13  Show Profile  Reply with Quote
Have you noticed that a LOT of people are now working with time card punch ins, and all seem to have the same problem with somebody not clocking IN or OUT ?

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 05/14/2007 :  11:18:06  Show Profile  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 05/14/2007 :  12:02:07  Show Profile  Reply with Quote
Yeah, well, that is exactly how I have to do that in DB2..it is very picky about this, and sometime the syntax, especially with the existant check, can get really hairy



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



Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 05/14/2007 :  16:36:34  Show Profile  Reply with Quote
I'm seriously not paid enough

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=83553

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



Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 05/14/2007 :  17:43:25  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Same guy, posting interview questions.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=83566

yikes. imagine if he gets hired? the sad thing is, someone here at SQLteam will give him the answers he needs ...

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 05/14/2007 :  18:13:53  Show Profile  Visit jezemine's Homepage  Reply with Quote
graz should change his title to "join hater"


www.elsasoft.org
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 05/14/2007 :  21:46:12  Show Profile  Reply with Quote
quote:
Originally posted by jsmith8858

Same guy, posting interview questions.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=83566

yikes. imagine if he gets hired? the sad thing is, someone here at SQLteam will give him the answers he needs ...

- Jeff
http://weblogs.sqlteam.com/JeffS




looks like he changed his mind on INNER JOIN, from hate to love ?



KH

Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 05/15/2007 :  00:01:26  Show Profile  Visit Merkin's Homepage  Reply with Quote
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
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 05/15/2007 :  01:38:54  Show Profile  Visit jezemine's Homepage  Reply with Quote
google's spider must crawl sqlteam pretty often...

http://www.google.com/search?q=i+hate+using+inner+joins


www.elsasoft.org
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 05/15/2007 :  04:43:43  Show Profile  Reply with Quote
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

Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 05/15/2007 :  09:13:41  Show Profile  Reply with Quote
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



Absolute power, corrupts absolutley





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



Go to Top of Page

mcrowley
Aged Yak Warrior

771 Posts

Posted - 05/15/2007 :  10:35:48  Show Profile  Reply with Quote
But it rocks absolutely, too:

http://www.despair.com/power.html
Go to Top of Page
Page: of 88 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000