niXforums Forum Index
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   PreferencesPreferences   Log in to check your private messagesLog in to check your private messages   Log inLog in 
·  nixdoc.net ·  man pages ·  Linux HOWTOs ·  FreeBSD Tips ·  Forums
navigation Forum index » Databases » IBM DB2
What is the difference in the update statements?
Post new topic   Reply to topic Page 1 of 1 [6 Posts] View previous topic :: View next topic
Author Message
Vincent M
*nix forums Guru


Joined: 22 Feb 2005
Posts: 1332

PostPosted: Mon Feb 14, 2005 2:42 pm    Post subject: Re: What is the difference in the update statements? Reply with quote
"Serge Rielau" <srielau@ca.ibm.com> wrote in message
news:37burnF57ctuiU1@individual.net...
Quote:
Thiru wrote:
Hi,
If performance is not at an issue, then what is the need to have
two different statement??

Thiru.
WantedToBeDBA.

The DB2 optimizer, among other things it does, is to perform query

optimization during which it converts your syntax to the most efficient DB2
syntax which is functionally equivalent to what you wrote.

You can see the optimized SQL when you do an explain.
Back to top
Serge Rielau
*nix forums Guru


Joined: 29 Apr 2005
Posts: 1583

PostPosted: Mon Feb 14, 2005 2:36 pm    Post subject: Re: What is the difference in the update statements? Reply with quote
Thiru wrote:
Quote:
Hi,
If performance is not at an issue, then what is the need to have
two different statement??

Thiru.
WantedToBeDBA.

Point for row assignment

SET (x, y, z) = (SELECT SUM(a), AVG(b), MIN(c) FROM T)

Point for column assignment:
SET x = (SELECT a FROM T1),
y = (SELECT b FROM T2),
z = (SELECT c FROM T3)

Beyond that.. freedom of choice. Whatever you like netter.

Cheers
Serge


--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Back to top
Knut Stolze
*nix forums Guru


Joined: 28 Jul 2005
Posts: 755

PostPosted: Mon Feb 14, 2005 8:30 am    Post subject: Re: What is the difference in the update statements? Reply with quote
Thiru wrote:

Quote:
Hi,
If performance is not at an issue, then what is the need to have
two different statement??

Some people like the first better than the second.

Similarly you could ask why subqueries are allowed in all situations, given
that you can write many queries using joins, or why BETWEEN exists, which
can be handled with two comparisons.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Back to top
Thiru
*nix forums addict


Joined: 20 May 2005
Posts: 80

PostPosted: Mon Feb 14, 2005 7:27 am    Post subject: Re: What is the difference in the update statements? Reply with quote
Hi,
If performance is not at an issue, then what is the need to have
two different statement??

Thiru.
WantedToBeDBA.
Back to top
Vincent M
*nix forums Guru


Joined: 22 Feb 2005
Posts: 1332

PostPosted: Mon Feb 14, 2005 6:59 am    Post subject: Re: What is the difference in the update statements? Reply with quote
"Thiru" <WantedToBeDBA@gmail.com> wrote in message
news:1108366818.298286.90460@z14g2000cwz.googlegroups.com...
Quote:
Hi all,
Here are the two update statement.

UPDATE stock SET status=NULL, quantity=0, price=0 WHERE type <> 'S'

UPDATE stock SET (status, quantity, price) = (NULL, 0, 0) WHERE type
'S'

What is the difference between the above 2 update statement? Which
statement will yield good performance?

Thanks,
Thiru.
WantedToBeDBA.

No difference in performance.


The performance will depend on a number of factors such as:

1. Is there an index on TYPE?
2. Cardinality of rows not = 'S' and size of the rows.
3. Is table clustered on TYPE?
Back to top
Thiru
*nix forums addict


Joined: 20 May 2005
Posts: 80

PostPosted: Mon Feb 14, 2005 6:40 am    Post subject: What is the difference in the update statements? Reply with quote
Hi all,
Here are the two update statement.

UPDATE stock SET status=NULL, quantity=0, price=0 WHERE type <> 'S'

UPDATE stock SET (status, quantity, price) = (NULL, 0, 0) WHERE type
<> 'S'

What is the difference between the above 2 update statement? Which
statement will yield good performance?

Thanks,
Thiru.
WantedToBeDBA.
Back to top
Google
Back to top
Display posts from previous:   
Post new topic   Reply to topic Page 1 of 1 [6 Posts] View previous topic :: View next topic
The time now is Thu Jan 08, 2009 2:39 am | All times are GMT
navigation Forum index » Databases » IBM DB2
Jump to:  

Similar Topics
Topic Author Forum Replies Last Post
No new posts C# MSSQL2000 update pln C 5 Fri Jul 21, 2006 7:41 am
No new posts HPSBMA02133 SSRT061201 rev.1 - HP Oracle for OpenView (Of... Security Alert HP-UX 0 Thu Jul 20, 2006 6:43 pm
No new posts HPSBMA02133 SSRT061201 rev.1 - HP Oracle for OpenView (Of... Security Alert HP-UX 0 Thu Jul 20, 2006 5:53 pm
No new posts Problem w/ Yast2, can't install new software or update Emmanuel Durand Suse 0 Thu Jul 20, 2006 5:50 am
No new posts FAQ 1.12 What's the difference between "perl" and "Perl"? PerlFAQ Server Perl 0 Wed Jul 19, 2006 7:03 pm

Mortgages | Mobile Phones | Credit Cards | Credit Counseling | Sailor Moon Music
Copyright © 2004-2005 DeniX Solutions SRL
 
Other DeniX Solutions sites: Unix/Linux blog |  electronics forum |  medicine forum |  science forum | 
Privacy Policy


Powered by phpBB © 2001, 2005 phpBB Group

[ Time: 0.1574s ][ Queries: 20 (0.0792s) ][ GZIP on - Debug on ]