Update statement question
-
CELKO (11/23/2012)
Here we go again. Dear J.C. you do need to calm down.
"!=" has nothing to do with 1970's Sybase and/or UNIX. Yes it was used old times, but it's still in use in modern technologies (eg. C# .NET). And I also, can tell you (keep it secret please) that will port easily into most of existing RDBMS's (eg. ORACLE)
Sorry, but I served on ANSI X3H2 for 10 years ...
So, what? It does mean absolutely nothing! If committee was stupid enough to explicitly reject "!=" syntax, it just tells about how outdated committee is (was). "!=" is no less logical than "<>" and it is in use by many cutting edge modern languages. I have nothing against using "<>", I just prefer "!=" and that is. No committee in the world (eg. United Nation) will stop me using it while MS SQL Server supports it.
BTW, I didn't reject the fact that "!=" was used by Sybase and UNIX back in 1970, if you read my words, I've said that this syntax was used in old days. What I was saying that use of "!=" right now has nothing to do with Sybase of 70's and UNIX. It just works in many modern languages, and it is very popular within people from C, C++, Java and C# background.
Actually, I wonder, which RDBMS system doesn't support "!=" (while supporting all other ANSI standards)? Please, someone, advise! I am desperate to know where I will not be able to port my code to...
-
Interesting discussion. What I don't get is - what's so special about looking like a hillbilly? Surely a hillbilly performs the task of looking like everyone else, namely using her/his eyes?
-
CELKO (11/27/2012)
what other alternative we can take here instead of "!= "
<> this has always been the ANSI/ISO Standard and it is univerally supported in every SQL I know
There have been several dialects in various SQLs !=, ^= and DB2 went nuts when they were pushing PL/I:...
Just matter of interest, do you know any RDBMS system which doesn't support "!=" while supporting all other ANSI standards?
-
CELKO (11/27/2012)
I don't remember punch cards but I do remember 8 inch floppy disks, if you had a 386 as a desktop you were lucky, and servers ran on 486 architectures and when 100Gb disk storage was considered excessive.
I have my old slide rulers in a wall display in my living room, and i still use an abacus to do my checkbook. In the real old days, we used an abacus for core dumps (Google it) because a rod can represent 0 to 15 😀
I must say I do feel as though I've made it here on SSC by being flamed by JC. 😛
Yes, it is a rite of passage 😉
You remember punch cards, 8 inches floppy disks, abacus and I think you should remember how magnetic toroids RAM looked like and how heavy were two handle discs for hard-drives of IBM 350... You have a great memory!
May be you could remember which RDBMS doesn't support "!=" while supporting all other ANSI SQL standards?
-
Looks like I will never get the answer I am looking for from JC...
Is any RDBMS around which doesn't support "!=" while supporting all other ANSI SQL standards?
May be I make my question a bit easier:
Is any RDBMS around which is fully ANSI SQL standards compliant?
I have heard that there is one, used somewhere in Sweden, but it's not widely used, cannot even track it down...
Any other ideas?
-
CELKO (11/27/2012)
I should have posted this in the previous posting. Hugo makes all the arguments.http://sqlblog.com/blogs/hugo_kornelis/archive/2008/03/09/lets-deprecate-update-from.aspx
I've seen all of his arguments and appreciate them but they still boil down to knowing what the code can and cannot do. In fact, I've used some of the so called "faults" of the proprietary form of UPDATE in T-SQL to overcome some pretty nasty problems.
Just like any other code, if you don't know what the code is doing or is going to do, people should probably choose a different profession. Even a SELECT technically sucks because it doesn't warn you that you haven't included a predicate or a sargable predicate. I wouldn't have it any other way.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
CELKO (11/28/2012)
Is any RDBMS around which is fully ANSI SQL standards compliant?
I have heard that there is one, used somewhere in Sweden, but it's not widely used, cannot even track it down...
Mimer. It also rejects ^= and ¬=. There are some small embedded SQL engines that are subsets of the SQL Standard, too. Mimer has a Validator tool on their website that will parse your code for SQL:2002, SQL-92 and SQL-99 conformance.
http://developer.mimer.com/validator/features/index.tml
It returns the feature levels used and usable error messages. I use it to check the code in my books at SQL:2003 and to check the code in my consulting work at SQL-99.
Congratulations! You have managed to google it out. That is the one I was referring to. Used a bit in Sweden and Norway (most of commercial users migrating it into other RDBMS's).
Now, lets see how many people around have heard about this great RDBMS!
However there are things about Mimer which may be disappointing for you...
Mimer SQL has things called "EXTENSIONS" which are not from ANSI SQL...
Examples of Mimer extensions:
ENTER
START
ALLOCATE CURSOR
REOPENABLE
ALTER DATABANK
ALTER DATABANK RESTORE
ALTER IDENT
ALTER SHADOW
CREATE BACKUP
SET DATABASE
SET SESSION
There are some which do correlate to MS SQL proprietary non-ANSI SQL syntax:
CREATE SYNONYM
UPDATE STATISTICS
Looks like your example of RDBMS is not as pure as you thought. Mimer does have some proprietary staff, same as other RDBMS's... :w00t:
But the worse come last:
You not read full documentation didn't you?
http://developer.mimer.com/documentation/pdf_82/sqlref.pdf
DEPRECATED FEATURES section:
Some non-standard features of earlier versions of Mimer SQL are retained for
backward compatibility
.
Read a bit down, looks like you are wrong again: Mime SQL does still support "!=" and some other:
Operator Standard form Alternative form(s)
string concatenation || &, !!
not equal to <> /=, !=, ¬=
less than or equal to <= ¬>
greater than or equal to >= ¬<
See, I'm quite proficient in googling things too 🙂
P.S. Would you seriously consider Mimer for enterprise solution? Looks like it's great thing for smartphone or like...
-
Hey, where are you? Mr. One Standard For All or You Can Port My Code To Alpha-Centaurus! :hehe:
I thought you will make another try...
Ok, I might help you a bit:
IBM Informix database can be created as ANSI compliant.
But it does suffer from the same "1970 Sybase/Unix, punch cards and magnetic type" problem, while supporting ANSI standards, it does still support Informix proprietorial extensions... Maximum what you can do is to turn another switch to report them in warnings...
-
Jeff Moden (11/27/2012)
CELKO (11/27/2012)
I should have posted this in the previous posting. Hugo makes all the arguments.http://sqlblog.com/blogs/hugo_kornelis/archive/2008/03/09/lets-deprecate-update-from.aspx
I've seen all of his arguments and appreciate them but they still boil down to knowing what the code can and cannot do. In fact, I've used some of the so called "faults" of the proprietary form of UPDATE in T-SQL to overcome some pretty nasty problems.
Just like any other code, if you don't know what the code is doing or is going to do, people should probably choose a different profession. Even a SELECT technically sucks because it doesn't warn you that you haven't included a predicate or a sargable predicate. I wouldn't have it any other way.
FYI
http://assets.red-gate.com/community/books/assets/defensive-database-programming.pdf
At the end of the first chapter there's an interesting discussion about UPDATE..FROM including a novel approach to forcing an ambiguous update to raise an error.
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
CELKO (11/27/2012)
The same holds true with UPDATE. If you don't know how to use it correctly, then it'll do weird things to you just like any other code.
No, the problem with the UPDATE..FROM.. syntax is fundamental. It has cardinality problems which it resolves in a non-deterministic fashion, based on the current indexing and physical storage ordering.
I've never had problems with cardinality. The result of a select statement once successfully parsed and checked for existance of referenced tables and rows will either return rows or it won't. I have also furthermore broke this down in to the select statement returning either one of the three: 0 rows, 1 row or more than one row. Now once I have figured this out, I've successfully used the UPDATE FROM construct without any cardinality problems.
I've encountered this thing of returning 0, 1, or many rows occurs so much with select statements during my relatively short time slinging SQL that I would have thought everyone here with any experience knew how to deal with it, so I'm pretty surprised by your insistance that theres some sort of "cardinality problem" with SQL? Isn't that like saying theres some sort of "wake up in the morning and go to work at a job" problem?
2 cents from a relative newb.
-
CELKO (11/27/2012)
It's not about to nor is it ever likely that the proprietary version of UPDATE will ever be deprecated because too many people have written code that uses it.
Gee, we said the same thing about *= and the first definition of BIT;-) Look at the number of MVPs, such as Hugo Cornelis for one, want it deprecated in favor of MERGE.
Yeah... that's why the CONNECT item was overwhelmingly voted down. And there are actually more MVPs that don't want to see UPDATE changed (I'm one of them) because the very cardinality "problems" you speak of are incredibly useful for handling certain things that shouldn't happen in a database, but do. They're just not as zealous or outspoken.
I don't know what the CONNECT item is but there are/were (haven't checked it in a while) some huge problems with MERGE.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply