update statement, two linking fields to other table
-
Gurus,
I've written many successful update statements to update a field based on a value in another table. I know to have the coordinating subquery and where clauses, but this one's got me stumped. I get an error that "Subquery returned more than 1 value...". I have tested the reference table for uniqueness. I do need two fields to get a unique record (InvoiceNo and TrackingNo), but the query looks like its ignoring the second linking field (InvoiceNo). ??? not sure, and I couldn't find any examples that had more than one linking field between the tables.
update F2R_B1ShipperInfo
set TrackingNo =
(select GroundTrackingNo from F2R_B1ShipperInfo A, F2R_GroundTrNo B
where A.TrackingNo = B.TrackingNo AND A.InvoiceNo = B.InvoiceNo)
WHERE EXISTS
(select GroundTrackingNo from F2R_B1ShipperInfo A, F2R_GroundTrNo B
where A.TrackingNo = B.TrackingNo AND A.InvoiceNo = B.InvoiceNo)
;
SQL Server 2000
-
A join you may prefer? Simplier it is, and is the way of the force! Futher more, use not the WHERE clause for Joins! That is the path to the dark side! Criteria alone you will put there in the future, hm?
A true jedi knows the ways of the JOINS .... to avoid joins when working with a relational database, is not the way of the jedi!
update
F2R_B1ShipperInfo
set TrackingNo = B.GroundTrackingNo
from
F2R_B1ShipperInfo A
inner join
F2R_GroundTrNo B
on A.TrackingNo = B.TrackingNo AND
A.InvoiceNo = B.InvoiceNo
Much simplier, is it not?
-
hmmm... never use proprietary features, hm?
SQL Security, he should not use! system stored procedures -- use them not! If he were programming for windows in C++, MFC classes he will not use! ANSI only, it must be! To someday port to MySQL, prepare we must!
Reminded perhaps he needs to be, what website he is on!
Yoda begins to question the mighty Celko's desire to teach, versus his desire to preach ... A fine line it is! ... Originates from the dark side, the latter does!
-
Reminded perhaps he needs to be, what website he is on!
Do a search here and you'll see this has been discussed before.
Btw Michael (right?), this Yoda nonsense talk style is only funny for a short period of time. Curious what the guys on sqlteam.com would think and write when you started posted there that way.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
A member not, of SQL Team yoda is, of this assured you can be...
But worry not -- Yoda is only visiting for a short time! gone soon, he will be. A busy jedi, indeed! hm.
-
Fair enough, and very well put!
-
Thanks for all the help. I enjoyed ALL the dialogue. To respond...
#1 I did an exhaustive search on this site (1.5 hrs) to find the answer before posting. Hang me for not finding it, but I sure did try.
#2 I had completed a query to ensure no duplicates existed using a [having count(*) > 1] clause even BEFORE I posted this issue. I believe the "compiler doesn't lie", but something wasn't adding up.
#3 I was not aware that "update...from" was proprietary. I have something new to ponder and enjoyed the debate. I always like to keep things to a standard.
#4 none of the fields were involved in the keys
I attacked the query again, reviewed what I've done in the past, and here's what worked:
update F2R_B1ShipperInfo
set F2R_B1ShipperInfo.TrackingNo = B.GroundTrackingNo
from F2R_B1ShipperInfo A, F2R_GroundTrNo B
where A.TrackingNo = B.TrackingNo AND A.InvoiceNo = B.InvoiceNo
;
Yoda, Frank & Celko, thanks for all your help.
Issue closed and resolved.
Go figure.
Live Long and Prosper (sic)
-
hmmm ... Yes, as Celko points out -- fundamental problem with your JOIN, there is. Select Max or Min or some other paritcular value from the joined table, you must.
Too many matching rows, for update to be deterministic!
Your original error message, hinted at larger problem it did! Missed by Yoda it was, but pointed out by Celko.
-
Why do I still have the strong feeling I know you?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply