What is the difference in the update statements?

[Thiru]

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.

[Mark A]

"Thiru" <Wa***********@gmail.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...

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?

[Thiru]

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

Thiru.
WantedToBeDBA.

[Knut Stolze]

Thiru wrote:

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

[Serge Rielau]

Thiru wrote:

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

[Mark A]

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:37*************@individual.net...

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.