January 7, 2009 at 4:20 pm
Joe:
Your point is that if you use UPDATE..FROM incorrectly you can produce incorrect results? I am pretty sure that that is a unversal truth, not something unique to UPDATE..FROM. I can think of dozens of ways to use ANSI/ISO Standard UPDATE incorrectly to produce a wide variety of incorrect results.
Should we be advising folks not to use SQL at all because it is "unreliable", given that using it incorrectly will give incorrect results?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 7, 2009 at 5:52 pm
Mr. Celko,
First, I'm not whining, far from it. I am simply pointing out a basic observation based on reading many of your posts both here on SSC and else where. It is due to your "holier than thou" attitude I have decided to never purchase any of your books, and rarely read any of the articles you have published.
I am not a cowboy programmer, but I will use to the best of my abilities the features of the hardware and software that I have to accomplish the goals of my organization. If that means writing code that is NOT 100% portable between OS's/compilers/RDBM's etc., then so be it. If it means rewriting that code several years later due to changes in hardware/software/OS's/RDBM's etc, again, so be it.
Unfortunately, that could happen even if you write 100% compatible code. You may have an internally developed system that has to be rewritten because you are to a completely NEW environment. Example a "green screen" system written in COBOL with ISAM databases to a WEB based ASP.NET using MS SQL Server 2005 for the database. What did writing 100% portable code buy you there?
Look at PeopleSoft. It is written to use ORACLE and MS SQL Server for its back end, but it can't use some SQL Server 2005's features because they aren't standard even though it would enhance the applications response time if it did.
January 7, 2009 at 5:54 pm
Joe Celko (1/7/2009)
>> Why do you have to be so acerbic? Not a good way to make friends and influence people. <<Quit being a whining child who expects its ignorance to be treated as equal to basic definitions, proof and decades of experience...
OK Joe, settle down. Lynn wasn't being whining or childish, he was being polite. And Lynn also has decades of experience and he certainly is not ignorant as evidenced by the quality of the thousands of posts that he has made in helping other people here.
Gee, I guess that is why I have not written eight books and 850+ articles on Standard SQL. And I have written tens of thousands of SQL statements that port in production code for 20+ years/
I have read most of them and they are great but that in no way justifies this kind of arrogance or name-calling. Lynn is just disagreeing with you, he is not impugning your character. The proper response is reasoned argument, not condescension.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 7, 2009 at 9:23 pm
RBarryYoung (1/7/2009)
Joe Celko (1/7/2009)
>> Why do you have to be so acerbic? Not a good way to make friends and influence people. <<Quit being a whining child who expects its ignorance to be treated as equal to basic definitions, proof and decades of experience...
OK Joe, settle down. Lynn wasn't being whining or childish, he was being polite. And Lynn also has decades of experience and he certainly is not ignorant as evidenced by the quality of the thousands of posts that he has made in helping other people here.
Gee, I guess that is why I have not written eight books and 850+ articles on Standard SQL. And I have written tens of thousands of SQL statements that port in production code for 20+ years/
I have read most of them and they are great but that in no way justifies this kind of arrogance or name-calling. Lynn is just disagreeing with you, he is not impugning your character. The proper response is reasoned argument, not condescension.
Thank you, Barry, for the kind words. Makes me wonder if I may have overreacted a bit in my response above.
January 8, 2009 at 6:48 am
Many thanks for all your comments. Mr. Celko I am a true SQL novice with many minutes of programming experience 🙂
Do not let the code posted mislead you to the integrity of the data.
In the past I have failed to include tables defs + sample code but have revised that approach and will do so whenever possible.
The tables relative to this post are intended for temporary use only (one off migration of values). The data types are as they are because that is how they imported from excel, I could have changed them but no need.
Hope that clears a few things up.
Keep up the good work, my future depends upon it!! 🙂
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
April 3, 2010 at 8:01 am
Many Many thanks for suggestion.
Great solution for many problems 🙂
Thanks a lot.
April 3, 2010 at 9:38 am
Only Joe could whine on about FIPS_FLAGGER and ANSI standards for a day and a half and then accuse someone else of being a whining child!
ANSI standard UPDATE syntax? No thanks!
Let's see: I want to UPDATE several columns, I wonder how this performs:
UPDATE Orders -- no schemas allowed!
SET some_col1 =
(
SELECT somecol1
FROM OrderDetails
WHERE OrderDetails.order_nbr = Orders.order_nbr
),
some_col2 =
(
SELECT somecol2
FROM OrderDetails
WHERE OrderDetails.order_nbr = Orders.order_nbr
),
some_col3 =
(
SELECT somecol3
FROM OrderDetails
WHERE OrderDetails.order_nbr = Orders.order_nbr
),
some_col4 =
(
SELECT somecol4
FROM OrderDetails
WHERE OrderDetails.order_nbr = Orders.order_nbr
)
WHERE EXISTS
(
SELECT *
FROM OrderDetails
WHERE OrderDetails.order_nbr = Orders.order_nbr
);
Note schema-qualification appears to be illegal in 'standard' SQL.
Newsflash: FIPS 127-2 has been rescinded.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 25, 2015 at 3:13 pm
I have two tables
1-Address_Master
IdAddress
1Puna
2UK
2-Address_Change
IdAddressDate
1agra 2015-07-16 01:02:09.160
1mathura2015-07-18 01:02:09.183
1delhi 2015-07-21 01:02:09.190
2noida 2015-07-15 01:02:09.197
2Agra 2015-07-24 01:02:09.203
And i want to update latest address from Address_Change table to Address_Master table order by date wise.
currently i am using below query, and this query is updating those records those are insert first. Please help me.
update A set A.address =C.Address
from Address_Master A
inner join Address_change C on A.id=C.id
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply