Trouble with Update syntax

[bolidev]

I'm new to SQL and can't figure out how to update my table
(StoreItemStatus) that contains the current status for items in each
store (STORE_KEY, ITEM_KEY, STATUS,...).
I get updated status info in a table (I'll call it NewInfo) that
has similar fields. NewInfo may contain multiple records for each
Store/Item, but I will just use the latest status. I'm not sure how
to update StoreItemStatus using each record of NewInfo. Any advice is
greatly appreciated

Thanks,
Paul

[David Portas]

You've said you want the "latest" status but you haven't told us how to
determine which the latest row is. I'll assume NewInfo has a DATETIME column
(status_dt) and that (store_key, item_key, status_dt) is unique.

UPDATE StoreItemStatus
SET status =
(SELECT status
FROM NewInfo AS N
WHERE store_key = StoreItemStatus.store_key
AND item_key = StoreItemStatus.item_key
AND status_dt =
(SELECT MAX(status_dt)
FROM NewInfo
WHERE store_key = N.store_key
AND item_key = N.item_key))

--
David Portas
SQL Server MVP
--

[Hugo Kornelis]

On Mon, 24 May 2004 07:08:14 +0100, David Portas wrote:

You've said you want the "latest" status but you haven't told us how to
determine which the latest row is. I'll assume NewInfo has a DATETIME column
(status_dt) and that (store_key, item_key, status_dt) is unique.

UPDATE StoreItemStatus
SET status =
(SELECT status
FROM NewInfo AS N
WHERE store_key = StoreItemStatus.store_key
AND item_key = StoreItemStatus.item_key
AND status_dt =
(SELECT MAX(status_dt)
FROM NewInfo
WHERE store_key = N.store_key
AND item_key = N.item_key))

Hi David,

Unless the OP is sure that every Store / Item combination has at least one
row in the NewInfo table, he'd better add

WHERE EXISTS
(SELECT *
FROM NewInfo
WHERE store_key = StoreItemStatus.store_key
AND item_key = StoreItemStatus.item_key)

to this query.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[bolidev]

Thank you! Your assumptions were correct, and I believe this will work.

[bolidev]

> Unless the OP is sure that every Store / Item combination has at least one

row in the NewInfo table, he'd better add

Hugo,

The NewInfo table will only contain a small subset of the Store/Item
combinations ( < 1k).
The StoreItemStatus table has ~500k records. I was considering using
cursors to iterate through the NewInfo records and updating the
associated StoreItemStatus records.
Is this a bad idea?

[David Portas]

> I was considering using

cursors to iterate through the NewInfo records and updating the
associated StoreItemStatus records.
Is this a bad idea?

Cursors are almost always a bad idea. Have you tried the WHERE EXISTS code
that Hugo posted?

--
David Portas
SQL Server MVP
--

[bolidev]

"David Portas" <RE****************************@acm.org> wrote in message news:<Kt********************@giganews.com>...

I was considering using
cursors to iterate through the NewInfo records and updating the
associated StoreItemStatus records.
Is this a bad idea?

Cursors are almost always a bad idea. Have you tried the WHERE EXISTS code
that Hugo posted?

Yes, I did implement the WHERE EXISTS, along with the code you
supplied,
and with a few minor tweaks everything worked great.
I haven't worked much with any RDBMS and the cursors were an easier
concept for me to grasp than the multiple nested queries, which is why
I asked about them.
I've learned a lot working through this problem, and I really
appreciate your help.

Thanks again,
PB

[Erland Sommarskog]

bo*****@hotmail.com (bo*****@hotmail.com) writes:

Yes, I did implement the WHERE EXISTS, along with the code you
supplied,
and with a few minor tweaks everything worked great.
I haven't worked much with any RDBMS and the cursors were an easier
concept for me to grasp than the multiple nested queries, which is why
I asked about them.
I've learned a lot working through this problem, and I really
appreciate your help.

If David's and Hugo's syntax was too contrived for you, here is
an alternative:

UPDATE StoreItemStatus
SET status = n.status
FROM StoreItemStatus s
JOIN NewInfo AS n ON n.store_key = s.store_key
AND n.item_key = s.item_key
JOIN (SELECT store_key, item_key, maxstatus = MAX(status_dt)
FROM NewInfo AS
GROUP BY store_key, item_key) AS nn
ON n.store_key = nn.store_key
AND n.item_key = nn.item_key
AND n.status_dt = nn.maxstatus

Except for the UPDATE SET part, this is how you would write a SELECT
statement to view the existing rows, and what the new values would be.

The thing in parenthesis is a derived table. Logically it is a temp
table in the middle of the query. However, SQL Server does not necessarily
materialize it, and may recast the computation order as long as the
result is the same.

One advantage of writing your UPDATE statements this way, is that you
don't easily do the same mistake as David did: by leaving on the
WHERE clause, he set the status to NULL for rows for which there are
no updates.

It should be said, however, that the above is a an extension to SQL,
which is available only in SQL Server, Sybase and Informix. David's
and Hugo's solution is ANSI-SQL, so it is likely to be more portable,
if that is an issue for you.

And while you still might find cursors easier to handled that SET-
based statments, beware of that the processing time for a cursor
can be several magnitudes slower. (And of the two ways of writing the
UPDATE statements, mine is likely to be faster than David's and Hugo's.)
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[David Portas]

In this case it appears that the proprietary UPDATE... FROM syntax is "safe"
because the OP has confirmed my assumption about the key. For the benefit of
the OP here's my own example of why MS's UPDATE...FROM syntax is so
incredibly BAD in my opinion and why I always try to avoid it.

CREATE TABLE Countries (countryname VARCHAR(20) PRIMARY KEY, capitalcity
VARCHAR(20) NULL)

CREATE TABLE Cities (cityname VARCHAR(20) NOT NULL, countryname VARCHAR(20)
NOT NULL REFERENCES Countries (countryname), CONSTRAINT PK_Cities PRIMARY
KEY (cityname, countryname))

INSERT INTO Countries (countryname) VALUES ('USA',NULL)
INSERT INTO Countries (countryname) VALUES ('UK',NULL)

INSERT INTO Cities VALUES ('Washington','USA')
INSERT INTO Cities VALUES ('London','UK')
INSERT INTO Cities VALUES ('Manchester','UK')

The MS-syntax makes it all too easy for the developer to slip-up by writing
ambiguous UPDATE...FROM statements where the JOIN criteria is not unique on
the right side of the join.

Try these two identical UPDATE statements with a small change to the primary
key in between.

UPDATE Countries
SET capitalcity = cityname
FROM Countries JOIN Cities /* evil UPDATE... FROM syntax */
ON Countries.countryname = Cities.countryname

SELECT * FROM Countries

ALTER TABLE Cities DROP CONSTRAINT PK_Cities
ALTER TABLE Cities ADD CONSTRAINT PK_Cities PRIMARY KEY (countryname,
cityname)

UPDATE Countries
SET capitalcity = cityname
FROM Countries JOIN Cities /* don't do this! */
ON Countries.countryname = Cities.countryname

SELECT * FROM Countries
I get this from the first SELECT statement:

countryname capitalcity
-------------------- --------------------
UK London
USA Washington

and this from the second:

countryname capitalcity
-------------------- --------------------
UK Manchester
USA Washington

(though these results aren't guaranteed - that's part of the problem).

Why did the result change? The physical implementation has affected the
meaning of the code, with serious, potentially disastrous consequences. How
can you even test your code if its results are subject to change due to the
vagaries of storage, indexing and cacheing?

With the ANSI syntax there is no ambiguity. The UPDATE statement compels the
programmer to design an unambiguous assignment subquery that returns no more
than a single value.

UPDATE Countries
SET capitalcity =
(SELECT MIN(cityname)
FROM Cities
WHERE Countries.countryname = Cities.countryname)

At the very least this forces the developer to reconsider whether the UPDATE
statement makes logical sense.

--
David Portas
SQL Server MVP
--

[bolidev]

Thank you for the education. I agree that that there is potential for
disaster if using the UPDATE...FROM syntax but it also appears much
easier to implement if updating multiple fields.
How do you handle this David? I am envisioning something like the
following:

UPDATE TargetTable
SET Field1 =
(SELECT...),
Field2 =
(SELECT...),
...

Where the UPDATE...FROM syntax (using Erland's example) might look
like this:

UPDATE StoreItemStatus
SET status = n.status, Field2 = n.Field2, Field3 = n.Field3, ...
FROM StoreItemStatus s
JOIN NewInfo AS n ON n.store_key = s.store_key
AND n.item_key = s.item_key
JOIN (SELECT store_key, item_key, maxstatus = MAX(status_dt)
FROM NewInfo AS
GROUP BY store_key, item_key) AS nn
ON n.store_key = nn.store_key
AND n.item_key = nn.item_key
AND n.status_dt = nn.maxstatus

[Erland Sommarskog]

David Portas (RE****************************@acm.org) writes:

The MS-syntax makes it all too easy for the developer to slip-up by
writing ambiguous UPDATE...FROM statements where the JOIN criteria is
not unique on the right side of the join.

Yes, using the FROM syntax in a sloppy way, you run the risk of updating
your rows in an unpredictable way. But since you easily can cast the
UPDATE into a SELECT statement, you can easily debug to see that you
have gone astray. And since people are used to write SELECT statement,
they probably have some sense for a key.

As your initial suggestion showed, the ANSI syntax is potentially
even more dangerous. If you don't understand the significance of
the WHERE EXISTS clause, of simply forget it, you may get very
predictable updates - but a lot more than you expect. That is, you
set the column to NULL for a bunch of rows. In my opinion, this is a
bigger distaster.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

bo*****@hotmail.com (bo*****@hotmail.com) writes:

How do you handle this David? I am envisioning something like the
following:

UPDATE TargetTable
SET Field1 =
(SELECT...),
Field2 =
(SELECT...),
...
In ANSI SQL, you can say

SET (Field1, Field2) = (SELECT col1, col2, ...)

But this is unfortunately not supported in SQL Server.
Where the UPDATE...FROM syntax (using Erland's example) might look
like this:

UPDATE StoreItemStatus
SET status = n.status, Field2 = n.Field2, Field3 = n.Field3, ...
FROM StoreItemStatus s
JOIN NewInfo AS n ON n.store_key = s.store_key
AND n.item_key = s.item_key
JOIN (SELECT store_key, item_key, maxstatus = MAX(status_dt)
FROM NewInfo AS
GROUP BY store_key, item_key) AS nn
ON n.store_key = nn.store_key
AND n.item_key = nn.item_key
AND n.status_dt = nn.maxstatus

And this is likely to perform better than the ANSI syntax, not the
least when you have several columns to update.

In fact, I have seen cases where the rewrite of a single:

UPDATE tbl
SET col = (SELECT SUM(...) FROM ...)
-- No where clause

into FROM clause reduced execution time from two minutes to a few
seconds. But there might well be queries for which the reverse
operation could have the same effect. (But when I see subqueries
in the SET/SELECT clause and I am into tuning, I usually try to
cast into FROM with success.)
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Serge Rielau]

Erland Sommarskog wrote:

David Portas (RE****************************@acm.org) writes:

The MS-syntax makes it all too easy for the developer to slip-up by
writing ambiguous UPDATE...FROM statements where the JOIN criteria is
not unique on the right side of the join.

Yes, using the FROM syntax in a sloppy way, you run the risk of updating
your rows in an unpredictable way. But since you easily can cast the
UPDATE into a SELECT statement, you can easily debug to see that you
have gone astray. And since people are used to write SELECT statement,
they probably have some sense for a key.

I just spend half a week convincing a customer that their existing
production system went astray on an UPDATE FROM.
As your initial suggestion showed, the ANSI syntax is potentially
even more dangerous. If you don't understand the significance of
the WHERE EXISTS clause, of simply forget it, you may get very
predictable updates - but a lot more than you expect. That is, you
set the column to NULL for a bunch of rows. In my opinion, this is a
bigger distaster.

While the EXIST is harder to understand an harder to get right, a foul
up in my experinec is blatant and will not pass any form of testing.
While the overwriting of a couple of rows is quit hard to detect.
Either way, the problem was achnowledged in SQL4 and the MERGE statement
has been introduced into ANSI SQL.
It works as such:
MERGE INTO T USING S ON T.PK = S.PK
WHEN MATCHED THEN UPDATE SET T.c1 = S.c1

S also can be any query. Merge is forbidden from updating the same row
twice.

To comment on bolidev's concern. ANSI SQL (and I trust TSQL) support:
SET (c1, ...) = (SELECT ....)
So teh repetiveness stops at 2. It's not getting worse with the number
of columns to be updated.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Erland Sommarskog]

Serge Rielau (sr*****@ca.eye-be-em.com) writes:

To comment on bolidev's concern. ANSI SQL (and I trust TSQL) support:
SET (c1, ...) = (SELECT ....)

No, this is not supported in T-SQL.


--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Serge Rielau]

Erland Sommarskog wrote:

Serge Rielau (sr*****@ca.eye-be-em.com) writes:

To comment on bolidev's concern. ANSI SQL (and I trust TSQL) support:
SET (c1, ...) = (SELECT ....)

No, this is not supported in T-SQL.

Pitty, makes sense though since UPDATE FROM covers the immediate need.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab