Re: Update or Delete querys with Joins
From: Grant Case (hangtime79_at_DONThSoPtAmMail.com)
Date: 02/06/05
- Next message: Vince: "Re: Help with SQL Query"
- Previous message: Vince: "Re: Help with SQL Query"
- In reply to: Steve Kass: "Re: Update or Delete querys with Joins"
- Next in thread: --CELKO--: "Re: Update or Delete querys with Joins"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 5 Feb 2005 22:03:23 -0600
> UPDATE T1 SET
> (A,B,C,D,E,F,G) = (
> SELECT (A,B,C,D,E,F,G)
> FROM T2
> WHERE T2.pk = T1.pk
This is were I was missing the boat Steve. Missed the fact that you could
update multiple columns in the ANSI with the same right-side of query. Well
if MS decides to implement this in a future version of T-SQL, I would have
no problem using it as there looks to be little downside to its use. For
now, I will be wary and use content to use the UPDATE...FROM construct until
that time as Microsoft deems it suitable for inclusion into T-SQL. Thanks
for making that clarification :).
Grant
"Steve Kass" <skass@drew.edu> wrote in message
news:O50khZ0CFHA.1296@TK2MSFTNGP10.phx.gbl...
> Grant,
>
> SQL-99 defines syntax for a multiple-row update:
>
> UPDATE T1 SET
> (A,B,C,D,E,F,G) = (
> SELECT (A,B,C,D,E,F,G)
> FROM T2
> WHERE T2.pk = T1.pk
> )
>
> but SQL Server doesn't implement it. SQL Server's UPDATE .. FROM
> can describe an update like this, but it can also describe a
> non-deterministic
> update, such as
>
> UPDATE T1 SET
> A = T2.A
> FROM MultiRowTable AS T2
>
> In my opinion, the fact that SQL Server raises no error if
> T2 contains more than one row (and more than one value
> of T2.A is a problem. Another quirk of the proprietary update
> is that these queries are identical in meaning:
>
> UPDATE T1 SET
> A = T2.A
> FROM T2
> WHERE T2.PK = T1.PK
>
> UPDATE T1 SET
> A = T2.A
> FROM T1, T2
> WHERE T2.PK = T1.PK
>
> As much as I like T-SQL, I think it's clear that much more thought
> went into the development of the standard to avoid quirks and
> ambiguities than did into developing T-SQL.
>
> Steve Kass
> Drew University
>
> Grant Case wrote:
>
>>Evening Joe,
>>
>>
>>
>>I have read these arguments and I still must respectfully disagree with
>>your assertion that the UPDATE...FROM statements are evil. I understand
>>the theoretical and physical implementation reasons as to why you, David,
>>and others have problems with the structure but I believe the speed of
>>development far outweighs the possible problem with creating a "Cartesian"
>>update.
>>
>>
>>
>>That is what were talking about essentially, an update Cartesian join. We
>>need to return one record to update our countries table, capitalcity
>>column and we return two. Since SQL Server has two rows that it can update
>>the column with, it updates twice and whatever row was last wins the
>>update.
>>
>>
>>
>>The problem I have against the ANSI syntax is that it creates more coding
>>then is necessary in order to fulfill the requirement. Good example of
>>this. Actually on a recommendation by you in this forum, I purchased
>>Advanced Data Generator for our company. I use ADG to create "pseudo" rows
>>to obfuscate such important information as borrower names, addresses,
>>account information, etc in our test environment. The final step in
>>disguising this information is updating the values in the test production
>>table from the pseudo rows table. Today this query looks something like
>>this:
>>
>>
>>
>> UPDATE Borrower
>>
>> SET BFaxTo = BrwFaxTo,
>>
>> BName = BrwName,
>>
>> BName2 = BrwName,
>>
>> BBankTitl1 = BrwName,
>>
>> BBankTitl2 = BrwName,
>>
>> BDeposit = LEFT(BrwName, 25),
>>
>> BSortKey = LEFT(BrwName, 15),
>>
>> BAddress1 = BrwAddress,
>>
>> BAddress2 = BrwAddress2,
>>
>> BCity = BrwCity,
>>
>> BState = BrwState,
>>
>> BZip = BrwZip,
>>
>> BFaxNumber = BrwFaxNumber,
>>
>> BMobile = BrwMobile,
>>
>> BPager = BrwPager,
>>
>> BTelephone = BrwTelephone,
>>
>> EmailAddr = BrwEmail,
>>
>> BTinTyp = BrwSSNorTID,
>>
>> BTaxID = BrwTaxID,
>>
>> BAccount = BrwAccount,
>>
>> BBankAcct1 = BrwBankAcct1,
>>
>> BBankAcct2 = BrwBankAcct2,
>>
>> BBankABANo = ABANumber,
>>
>> BBankAdd = Address,
>>
>> BBankCity = City,
>>
>> BBankName = BankName,
>>
>> BBankState = State,
>>
>> BBankShort = LEFT(BankName, 18),
>>
>> BSpecIns1 = '',
>>
>> BSpecIns2 = ''
>>
>> FROM Borrower
>>
>> INNER JOIN ScrubBorrowerRef ON BrwNumber = Bnumber
>>
>>
>>
>>Pretty simple query, I would say fairly easy to follow even for someone
>>who may not know Transact-SQL. Now let's write this in an ANSI-style
>>UPDATE.
>>
>>
>>
>> UPDATE Borrower
>>
>> SET BFaxTo = (SELECT BrwFaxTo FROM ScrubBorrowerRef WHERE
>> BrwNumber = Bnumber),
>>
>> BName = (SELECT BrwName FROM ScrubBorrowerRef
>> WHERE BrwNumber = Bnumber),
>>
>> BName2 = (SELECT BrwName FROM ScrubBorrowerRef
>> WHERE BrwNumber = Bnumber),
>>
>> BBankTitl1 = (SELECT BrwName FROM ScrubBorrowerRef
>> WHERE BrwNumber = Bnumber),
>>
>> BBankTitl2 = (SELECT BrwName FROM ScrubBorrowerRef
>> WHERE BrwNumber = Bnumber),
>>
>> BDeposit = (SELECT LEFT(BrwName, 25) FROM
>> ScrubBorrowerRef WHERE BrwNumber = Bnumber),
>>
>> BSortKey = (SELECT LEFT(BrwName, 15) FROM
>> ScrubBorrowerRef WHERE BrwNumber = Bnumber),
>>
>> BAddress1 = (SELECT BrwAddress FROM
>> ScrubBorrowerRef WHERE BrwNumber = Bnumber),
>>
>> BAddress2 = (SELECT BrwAddress2 FROM
>> ScrubBorrowerRef WHERE BrwNumber = Bnumber),
>>
>> BCity = (SELECT BrwCity FROM ScrubBorrowerRef
>> WHERE BrwNumber = Bnumber),
>>
>> BState = (SELECT BrwState FROM ScrubBorrowerRef
>> WHERE BrwNumber = Bnumber),
>>
>> BZip = (SELECT BrwZip FROM ScrubBorrowerRef WHERE
>> BrwNumber = Bnumber),
>>
>> BFaxNumber = (SELECT BrwFaxNumber FROM
>> ScrubBorrowerRef WHERE BrwNumber = Bnumber),
>>
>> BMobile = (SELECT BrwMobile FROM ScrubBorrowerRef
>> WHERE BrwNumber = Bnumber),
>>
>> BPager = (SELECT BrwPager FROM ScrubBorrowerRef
>> WHERE BrwNumber = Bnumber),
>>
>> BTelephone = (SELECT BrwTelephone FROM
>> ScrubBorrowerRef WHERE BrwNumber = Bnumber),
>>
>> EmailAddr = (SELECT BrwEmail FROM ScrubBorrowerRef
>> WHERE BrwNumber = Bnumber),
>>
>> BTinTyp = (SELECT BrwSSNorTID FROM
>> ScrubBorrowerRef WHERE BrwNumber = Bnumber),
>>
>> BTaxID = (SELECT BrwTaxID FROM ScrubBorrowerRef
>> WHERE BrwNumber = Bnumber),
>>
>> BAccount = (SELECT BrwAccount FROM
>> ScrubBorrowerRef WHERE BrwNumber = Bnumber),
>>
>> BBankAcct1 = (SELECT BrwBankAcct1 FROM
>> ScrubBorrowerRef WHERE BrwNumber = Bnumber),
>>
>> BBankAcct2 = (SELECT BrwBankAcct2 FROM
>> ScrubBorrowerRef WHERE BrwNumber = Bnumber),
>>
>> BBankABANo = (SELECT ABANumber FROM
>> ScrubBorrowerRef WHERE BrwNumber = Bnumber),
>>
>> BBankAdd = (SELECT Address FROM ScrubBorrowerRef
>> WHERE BrwNumber = Bnumber),
>>
>> BBankCity = (SELECT City FROM ScrubBorrowerRef
>> WHERE BrwNumber = Bnumber),
>>
>> BBankName = (SELECT BankName FROM ScrubBorrowerRef
>> WHERE BrwNumber = Bnumber),
>>
>> BBankState = (SELECT State FROM ScrubBorrowerRef
>> WHERE BrwNumber = Bnumber),
>>
>> BBankShort = (SELECT LEFT(BankName, 18) FROM
>> ScrubBorrowerRef WHERE BrwNumber = Bnumber),
>>
>> BSpecIns1 = '',
>>
>> BSpecIns2 = ''
>>
>>
>>
>>I would say that my chances of making a mistake within this ANSI-style
>>UPDATE versus the UPDATE.FROM syntax are better then five-fold.
>>
>>
>>
>>I agree with you in that the UPDATE.FROM can have some fairly negative
>>results, but I believe that risk is manageable because the problem starts
>>between the ears of the developer. If you can maintain 1 + 1 relationships
>>then you should have no problem with the UPDATE.FROM structure. Having
>>tutored a few developers in my time (you probably have 10000x fold on me
>>at this point), each has learned this lesson quickly and have had no
>>problem grasping the gravity of the situation. That said, I'm not
>>comfortable with the extraneous amount of code necessary to solve the same
>>problem in an ANSI-style statement. I think the ANSI-style update leads to
>>more potential errors then it solves. For the record, I am a huge backer
>>of the ANSI-style JOIN structures because I believe they lend themselves
>>to understanding the query construction better. In this case, I believe
>>the opposite holds true for the ANSI-style updates.
>>
>>
>>
>>
>>
>>"--CELKO--" <jcelko212@earthlink.net> wrote in message
>>news:1107560326.546412.94720@l41g2000cwc.googlegroups.com...
>>
>>>Here is a detailed example from David Portas as to why MS's
>>>UPDATE...FROM syntax is so incredibly bad:
>>>
>>>CREATE TABLE Countries
>>>(countryname VARCHAR(20) NOT NULL PRIMARY KEY,
>>>capitalcity VARCHAR(20));
>>>
>>>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, capitalcity) VALUES ('USA', NULL);
>>>INSERT INTO Countries (countryname, capitalcity) 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;
>>>
>>>You 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. You might want to make an effort
>>>to learn Standard SQL instead of a dialect that can change at any time,
>>>which will not port, cannot be understood by other programmers, etc.
>>>
>>>
>>
>>
>>
- Next message: Vince: "Re: Help with SQL Query"
- Previous message: Vince: "Re: Help with SQL Query"
- In reply to: Steve Kass: "Re: Update or Delete querys with Joins"
- Next in thread: --CELKO--: "Re: Update or Delete querys with Joins"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
