473,385 Members | 1,958 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,385 software developers and data experts.

Trouble with Update syntax

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
Jul 20 '05 #1
14 2477
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
--
Jul 20 '05 #2
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)
Jul 20 '05 #3
Thank you! Your assumptions were correct, and I believe this will work.
Jul 20 '05 #4
> 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?
Jul 20 '05 #5
> 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
--
Jul 20 '05 #6
"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
Jul 20 '05 #7
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
Jul 20 '05 #8
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
--

Jul 20 '05 #9
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
Jul 20 '05 #10
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
Jul 20 '05 #11
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
Jul 20 '05 #12
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
Jul 20 '05 #13
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
Jul 20 '05 #14
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
Jul 20 '05 #15

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Dave | last post by:
I have 2 tables, one with names, and another with addresses, joined by their CIVICID number (unique to the ADDRESSINFO table) in Oracle. I need to update a field in the NAMEINFO table for a...
2
by: Fork | last post by:
Hi, I am trying to execute a script from the command line as follows; c:\program files\mysql\bin>mysql -u root newsman < servers3.mysql But this gives me the following error; ERROR 1064...
17
by: kalamos | last post by:
This statement fails update ded_temp a set a.balance = (select sum(b.ln_amt) from ded_temp b where a.cust_no = b.cust_no and a.ded_type_cd = b.ded_type_cd and a.chk_no = b.chk_no group by...
6
by: HeadScratcher | last post by:
I am trying to speed up my update statements by removing inner select statements. Example: update orders set shipname = (select contactName from customers where customerid = orders.customerID)...
3
by: Jerry | last post by:
Well, here is some weirdness. First, I noticed that I have 2 Set keywords (silly me). so I removed the 2nd "Set" but still got a syntax error. Then I removed the Where clause, and now it works...
7
by: Gary Paris | last post by:
I have a bound datagrid on my windowsform. I have the following declarations in the global module of the project: Public eDS As DataSet Public eDA As OleDb.OleDbDataAdapter In the Load...
3
by: Ciara9 | last post by:
I am having problems trying to update a field in a database using a field in a form. I currently have two fields, Today and Tomorrow in a table named Date. The Today field automatically defaults to...
1
by: sibusiso | last post by:
HI Can Any one help I have extra field on a table like FDate, FYear, FMonth, FDay, FDatename I have a triger that I will update this field every time transaction hapened, this field must...
5
matheussousuke
by: matheussousuke | last post by:
Hello, I'm using tiny MCE plugin on my oscommerce and it is inserting my website URL when I use insert image function in the emails. The goal is: Make it send the email with the URL...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.