Trouble with Update syntax

[bolidev]

I'm new to SQL and can't figure out how to update my table
(StoreItemStatu s) 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******* *************@g iganews.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*****@hotmai l.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.count ryname = Cities.countryn ame

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.count ryname = Cities.countryn ame

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.count ryname = Cities.countryn ame)

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