CELKO (1/14/2011)
Tom: MERGE gives an error because it has to by definition. Values in a column are scalars; this is part of First Normal Form. I cannot put multiple scalars into that column. An UPDATE has to be deterministic by definition, like any relational operator, so I cannot put in a random value. An UPDATE that has a match has to be completed, so I cannot skip it. The only solutionis to report the truth -- we have a problem, Houston!
Yes, as I said before I'm happy with it giving an error - and incidentally I'd be happy is UPDATE...FROM did the same. Although the Merge syntax has some advantages over UPDATE...FROM it also has a disadvantage - it isn't backwards compatible with SQL 2005 or SQL 2000 and from my experience of trying to get customers to pay for SQL upgrades people will be stuck with maintaining systems using these versions long after the end of standard support.
Ever work with PICK or other NFNF databases? They can keep multiple scalars in a column.
NFNF databases? I once inherited an SQL database that contained an NFNF table! The first thing I did was to make all code that I could control that operated on that table convert anyrelevant rows into a FNF temporary table, do the required manipulations without any NFNF nonsense, and then update affected rows in the original table by converting back to SRF (Standard Rubbish Form). I am only able to account for the existence of NFNF relational databases in the same way as I account for people preferring COM to .NET (ie by attributing it to the indominitable stupidity of developers).
Also, if you played with Sybase, their version of UPDATE. FROM.. was a little different at one time. They made a total out of the duplicate matches and over-wrote the old value with it.
That's awful. Of course I can do that in T-SQL very easily using either "quirky update" (which may require safeguards if the aggregation required is non-commutative or non-associative) or by doing an aggregation using group by, whether I use Merge or Update...From it is extremely straight forward to do. But that it should be done behind the scenes withouut a clear indication that the author of the query required it - that really is awful.
Jeff, the old stuff I used to post before MERGE did blow up in the proper fashion.
UPDATE Orders
SET some_col
= (SELECT item_price
FROM OrderDetails
WHERE OrderDetails.order_nbr = Orders.order_nbr)
WHERE EXISTS
(SELECT *
FROM OrderDetails
WHERE OrderDetails.order_nbr = Orders.order_nbr);
I like that - it's nice and clean.
This will of course result in an error, and even the most junior of junior programmers will eventually figure out (probably by asking a senior) what's wrong. At that point, either the query is corrected to match the request, or a note is sent back to management asking for a clarification of the ambiguity in the request.
Since there is a move among MVPs to deprecate this in favor of MERGE and no other product uses it, I would clean up my code and get with the 21-sst Century.
Deprecating Update...From in favour of Merge would be crazy - the right thing to do is deprecate Update altogether, since it can't do anything Merge can't do. It would probably be a good idea to give it a few years for the development community out there to discover Merge before deprecating Update though.
edit: getting the quote brackets right.
Tom