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