ORDER BY Should be same as my input in IN()
-
CELKO (12/21/2012)
What on this good green Earth are you talking about??? Normal updates don't rely on index order. Only "Quirky Updates" rely on that and THAT's NOT the reason why people are trying to get rid of UPDATE FROM nor does it require that "all indexes have to be kept constant".
The old 1970's Sybase syntax is unpredictable, not portable and conceptually wrong.
CREATE TABLE Orders
(order_nbr INTEGER NOT NULL PRIMARY KEY,
some_col DECIMAL (9,2) NOT NULL);
INSERT INTO Orders
VALUES (1, 0), (2, 0), (3, 0);
CREATE TABLE Order_Details
(order_nbr INTEGER NOT NULL,
sku INTEGER NOT NULL,
item_price DECIMAL (9,2) NOT NULL,
PRIMARY KEY(order_nbr, sku),
-- FOREIGN KEY(sku) REFERENCES Products(sku)
FOREIGN KEY(order_nbr) REFERENCES Orders(order_nbr));
INSERT INTO Order_Details VALUES (1, 1, 500.00);
INSERT INTO Order_Details VALUES (1, 2, 205.00);
INSERT INTO Order_Details VALUES (2, 1, 490.95);
INSERT INTO Order_Details VALUES (3, 1, 480.00);
SELECT * FROM Orders;
UPDATE Orders
SET Orders.some_col =Order_Details.item_price
FROM Orders
INNER JOIN
Order_Details
ON Orders.order_nbr =Order_Details.order_nbr;
/* results -- see item #1; last physical value
1205.00 - where is the $500.00?
2490.95
3480.00
*/
--repeat with new physical ordering
DELETE FROM Order_Details;
DELETE FROM Orders;
DROP INDEX Order_Details.foobar;
-- index will change the execution plan
CREATE INDEX foobar ON Order_Details (order_nbr, item_price);
INSERT INTO Orders VALUES (1, 0);
INSERT INTO Orders VALUES (2, 0);
INSERT INTO Orders VALUES (3, 0);
INSERT INTO Order_Details VALUES (1, 2, 205.00);
INSERT INTO Order_Details VALUES (1, 1, 500.00);
INSERT INTO Order_Details VALUES (2, 1, 490.95);
INSERT INTO Order_Details VALUES (3, 1, 480.00);
UPDATE Orders
SET Orders.some_col = Order_Details.item_price
FROM Orders
INNER JOIN
Order_Details
ON Orders.order_nbr = Order_Details.order_nbr;
SELECT * FROM Orders;
/*
Results
1500.00
2490.95
3480.00
*/
What is the first property that you must have in an INDEX? It cannot change the results of a statement, only the performance. See the problem?
We have the ANSI/ISO Standard MERGE statement which will catch this and other problems. Use it.
What is conceptually illogical is your purported example. I mean, really, who in there right mind would populate a column in an Order Header table with a single value from a detail record of an order?
The correct code for this would be this:
UPDATE Orders SET
Orders.some_col = SUM(Order_Details.item_price)
FROM
Orders
INNER JOIN Order_Details
ON Orders.order_nbr = Order_Details.order_nbr;
Once again, it comes to knowing what you are doing. I'm sorry, I find the way that MS SQL Server does things with UPDATE FROM actually quite logical. Too bad you can't see it.
-
Lynn Pettis (12/21/2012)
Jeff Moden (12/21/2012)
Josh Ashwood (12/20/2012)
GilaMonster (12/20/2012)
Can you imagine all the simple
SELECT Foo FROM Foo, Bar WHERE Foo.X *= Bar.Y
haha *=
Now that is real dinosaur stuff!!
Can you imagine the unspeakable evils Joe has committed in his time before the advent of SQL-92?? :w00t:
BWAAA-HAAA!!!! How about the ones he still does like storing a "year" as '2012-00-00' .
But MySQL supports this and properly sorts it with other valid dates! Ask Mr. Celko, he'll be more than happy to tell you that. Oh what, he does every time someone posts a question with dates involved.
Oh yes... I get that MySQL supports such a thing. But SQL Server does not and since this is an SQL Server forum, I wish he'd stop posting (ironically) code that doesn't port to SQL Server correctly.
--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 (12/21/2012)
What on this good green Earth are you talking about??? Normal updates don't rely on index order. Only "Quirky Updates" rely on that and THAT's NOT the reason why people are trying to get rid of UPDATE FROM nor does it require that "all indexes have to be kept constant".
The old 1970's Sybase syntax is unpredictable, not portable and conceptually wrong.
CREATE TABLE Orders
(order_nbr INTEGER NOT NULL PRIMARY KEY,
some_col DECIMAL (9,2) NOT NULL);
INSERT INTO Orders
VALUES (1, 0), (2, 0), (3, 0);
CREATE TABLE Order_Details
(order_nbr INTEGER NOT NULL,
sku INTEGER NOT NULL,
item_price DECIMAL (9,2) NOT NULL,
PRIMARY KEY(order_nbr, sku),
-- FOREIGN KEY(sku) REFERENCES Products(sku)
FOREIGN KEY(order_nbr) REFERENCES Orders(order_nbr));
INSERT INTO Order_Details VALUES (1, 1, 500.00);
INSERT INTO Order_Details VALUES (1, 2, 205.00);
INSERT INTO Order_Details VALUES (2, 1, 490.95);
INSERT INTO Order_Details VALUES (3, 1, 480.00);
SELECT * FROM Orders;
UPDATE Orders
SET Orders.some_col =Order_Details.item_price
FROM Orders
INNER JOIN
Order_Details
ON Orders.order_nbr =Order_Details.order_nbr;
/* results -- see item #1; last physical value
1205.00 - where is the $500.00?
2490.95
3480.00
*/
--repeat with new physical ordering
DELETE FROM Order_Details;
DELETE FROM Orders;
DROP INDEX Order_Details.foobar;
-- index will change the execution plan
CREATE INDEX foobar ON Order_Details (order_nbr, item_price);
INSERT INTO Orders VALUES (1, 0);
INSERT INTO Orders VALUES (2, 0);
INSERT INTO Orders VALUES (3, 0);
INSERT INTO Order_Details VALUES (1, 2, 205.00);
INSERT INTO Order_Details VALUES (1, 1, 500.00);
INSERT INTO Order_Details VALUES (2, 1, 490.95);
INSERT INTO Order_Details VALUES (3, 1, 480.00);
UPDATE Orders
SET Orders.some_col = Order_Details.item_price
FROM Orders
INNER JOIN
Order_Details
ON Orders.order_nbr = Order_Details.order_nbr;
SELECT * FROM Orders;
/*
Results
1500.00
2490.95
3480.00
*/
What is the first property that you must have in an INDEX? It cannot change the results of a statement, only the performance. See the problem?
We have the ANSI/ISO Standard MERGE statement which will catch this and other problems. Use it.
I very much appreciate the effort you put into writing this code and you've posted it before. My response is also the same as before. It only proves only that someone screwed up an wrote incorrect code. Someone forgetting to use SUM is as bad as someone forgetting to use a WHERE clause on a DELETE... and that doesn't give you any warning, either.
So far as I'm concerned, MERGE is an unnecessary complication for a simple thing (replacement for UPDATE). The only place that I'd use it is to actually replace UPDATEs on engines, like Oracle, that don't have an UPDATE with a FROM clause or when a true MERGE actually needs to be accomplished.
--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) -
Jeff Moden (12/21/2012)
Lynn Pettis (12/21/2012)
Jeff Moden (12/21/2012)
Josh Ashwood (12/20/2012)
GilaMonster (12/20/2012)
Can you imagine all the simple
SELECT Foo FROM Foo, Bar WHERE Foo.X *= Bar.Y
haha *=
Now that is real dinosaur stuff!!
Can you imagine the unspeakable evils Joe has committed in his time before the advent of SQL-92?? :w00t:
BWAAA-HAAA!!!! How about the ones he still does like storing a "year" as '2012-00-00' .
But MySQL supports this and properly sorts it with other valid dates! Ask Mr. Celko, he'll be more than happy to tell you that. Oh what, he does every time someone posts a question with dates involved.
Oh yes... I get that MySQL supports such a thing. But SQL Server does not and since this is an SQL Server forum, I wish he'd stop posting (ironically) code that doesn't port to SQL Server correctly.
I guess my sarcasm got lost in translation.....
I fully agree with you, Jeff.
-
And OP only wanted to sort his SELECT...
😉
But on this forum we do love to go dip! :hehe:
Merry Christmas to everyone 😀
-
CELKO (12/22/2012)
What is conceptually illogical is your purported example.
Set theory? Foundation of the Relational Model? First Normal Form (1NF) and the Information Principle derived from it, states that all information is modeled as single scalar values in a column of row in a table. The UPDATE..FROM.. violates the cardinality axioms by trying to put multiple scalar values into a column.
Again, for the highly intelligent yet blind, your example is illogical. No one is going to put a single dollar amount from a detail line item of an order into the header record of the order. There is NO BUSINESS REQUIREMENT FOR THIS TO OCCUR. That is precisely what you are attempting to do with your example and it is a perfectly great example of someone not writing the correct code. I posted the code that should be used to correctly populate the column in the header by using the SUM function.
Do you need a hammer and saw?
-
Celko original query (formatting is mine):
UPDATE Orders SET
Orders.some_col = Order_Details.item_price
FROM
Orders
INNER JOIN Order_Details
ON Orders.order_nbr = Order_Details.order_nbr;
Let us rewrite it in Standard SQL:
UPDATE Orders SET
some_col = (select Order_Details.item_price
from Order_Details
where Orders.order_nbr = Order_Details.order_nbr);
Wait, this won't work as it will error out if there is more than one
value returned by the subquery. What do most people do at this point? This:
UPDATE Orders SET
some_col = (select top 1 Order_Details.item_price
from Order_Details
where Orders.order_nbr = Order_Details.order_nbr);
That will do same thing as Celko's code above and who knows which value you will
get if there are multiple detail line items for each order because we don't have
an order by in the subquery.
So, what is the correct code here? This:
UPDATE Orders SET
some_col = (select sum(Order_Details.item_price)
from Order_Details
where Orders.order_nbr = Order_Details.order_nbr);
Oh, wait, doesn't that do the same thing as this?
UPDATE Orders SET
Orders.some_col = SUM(Order_Details.item_price)
FROM
Orders
INNER JOIN Order_Details
ON Orders.order_nbr = Order_Details.order_nbr;
Do you see how illogical your example is now Mr. Celko? You can screw it up just as easily using Standard SQL as you can using the T-SQL variant method.
-
CELKO (12/22/2012)
Why don't you understand that a programming language is based on syntax, without regard to any semantics that you know from the human view point? Gee, I guess we do not need to get an error from division by zero in any programming languages. Who in their right mind would divide by zero? So, in your world, we can pick a random number and keep going? I like 42 because of Douglas Adams!And this? I do understand. I am a highly educated person. I have a bachelors degree in Computer Science so I have an understanding of the theories you speak of. I also understand that you have to apply all this to real world problems using the tools you have available.
-
CELKO (12/22/2012)
If you are confused by the table and column names in the example, then change them to abstracts. The point that you seem to miss is that a 1:m relationship does not fit into a single column.Hey, in the real world, putting the sum of the details in the header would be a non-normal form redundancy.
You are missing the point. If that occurs it is normally because someone messed up the requirements some how. You need to come up with a true example where this may occur.
As for storing a computed value (non-normal form redundancy) is usually done as a controlled denormalization to improve reporting. I am guilty of doing this in some designs. When you make those decisions, you have to know the trade offs for doing it.
Some times this is needed in the real world.
Try coming up a more valid example for the future when you want to have this argument.
As for me, I'm tired of your continued pompous and arrogant attitude and I am really tired of being talked down to by people like you, so please just go away and leave us all alone.
-
CELKO (12/22/2012)
UPDATE Orders SET
some_col = (SELECT TOP 1 Order_Details.item_price
FROM Order_Details
WHERE Orders.order_nbr = Order_Details.order_nbr);
I HOPE NOT! The proprietary TOP (n) without an ORDER BY [ASC|DESC] is also unpredictable. What if we really wanted the second item_price? The incompetent programmer has just replaced one flawed proprietary “feature” with another flawed proprietary “feature” to hide a serious problem.
A competent programmer would get the error message and panic. It shows that the database has inconsistent data. That means the schema is screwed! First, I need to clean up the Order_Details table and add constraints to it. Is this recent or has it been going on for awhile?
OMG, you are so intelligent you missed the comment I had after the code above because I had already made the comment about not having an order by in the subquery:
That will do same thing as Celko's code above and who knows which value you will
get if there are multiple detail line items for each order because we don't have
an order by in the subquery.
Really, just go away. Go find an Oracle forum to haunt. They do things just as wrong as SQL Server DBA's and Developers.
By the way, I am not going to use the MERGE statement where an UPDATE statement will do the job. The MERGE makes sense where you would have to use INSERT/UPDATE/DELETE statements together to accomplish a specific process.
-
Do you see how illogical your example is now Mr. Celko? You can screw it up just as easily using Standard SQL as you can using the T-SQL variant method.
Agreed, here's another unpredictable UPDATE, this one using standard SQL. Just like UPDATE...FROM you'll get random results and no error message. If any of my developers did this, I'd shoot them on the spot.
WITH Order_DetailsCTE AS (SELECT item_price,order_nbr,
ROW_NUMBER() OVER(PARTITION BY order_nbr ORDER BY order_nbr) AS rn
FROM Order_Details)
UPDATE Orders SET
some_col = (select Order_DetailsCTE.item_price
from Order_DetailsCTE
where Orders.order_nbr = Order_DetailsCTE.order_nbr
and Order_DetailsCTE.rn = 1);
____________________________________________________
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
-
hum...interesting...topic..discussion..alng with some snow fight...
karthik
-
All hail Celko!!!!!
:smooooth:
-
Josh Ashwood (12/26/2012)
All hail Celko!!!!!:smooooth:
Please, do not encourage him! We want him to go haunt an Oracle site for a while.
-
Lynn Pettis (12/26/2012)
Josh Ashwood (12/26/2012)
All hail Celko!!!!!:smooooth:
Please, do not encourage him! We want him to go haunt an Oracle site for a while.
Speak for yourself. You let him get under your skin too easily, but others like myself find value in his posts. If you disagree, make your point and move on. You're not going to win these petty battles.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
Viewing 15 posts - 46 through 60 (of 60 total)
You must be logged in to reply to this topic. Login to reply