Using SQL-2000
DROP TABLE OrderDetails, Orders;
CREATE TABLE Orders
(order_nbr INTEGER NOT NULL PRIMARY KEY,
some_col DECIMAL (9,2) NOT NULL);
INSERT INTO Orders VALUES (1, 0);
INSERT INTO Orders VALUES (2, 0);
INSERT INTO Orders VALUES (3, 0);
CREATE TABLE OrderDetails
(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 OrderDetails VALUES (1, 1, 500.00);
INSERT INTO OrderDetails VALUES (1, 2, 205.00);
INSERT INTO OrderDetails VALUES (2, 1, 490.95);
INSERT INTO OrderDetails VALUES (3, 1, 480.00);
UPDATE Orders
SET Orders.some_col = OrderDetails.item_price
FROM Orders
INNER JOIN
OrderDetails
ON Orders.order_nbr = OrderDetails.order_nbr;
/*
results -
1 205.00 - where is the $500.00?
2 490.95
3 480.0
*/
--repeat with new physical ordering
DELETE FROM OrderDetails;
DELETE FROM Orders;
DROP INDEX OrderDetails.foobar;
--this will change the INNER JOIN
CREATE INDEX foobar ON OrderDetails (order_nbr, item_price);
INSERT INTO Orders VALUES (1, 0);
INSERT INTO Orders VALUES (2, 0);
INSERT INTO Orders VALUES (3, 0);
INSERT INTO OrderDetails VALUES (1, 2, 205.00);
INSERT INTO OrderDetails VALUES (1, 1, 500.00);
INSERT INTO OrderDetails VALUES (2, 1, 490.95);
INSERT INTO OrderDetails VALUES (3, 1, 480.00);
-- run it again
UPDATE Orders
SET Orders.some_col = OrderDetails.item_price
FROM Orders
INNER JOIN
OrderDetails
ON Orders.order_nbr = OrderDetails.order_nbr;
SELECT * FROM Orders;
/*
Results
1 500.00 -- now the other guy shows up!
2 490.95
3 480.00
*/
What property is most important in an INDEX? Adding or dropping an
index changes performance but never, never changes results!
Since anyone can now cut & paste this into QA, and hit the F5 key, do
I even have to reply?
If you had worked with the original Sybase version of this syntax, you
would have been told it was a replacement for looping. You would
written code like "SET Orders.some_col = Orders.some_col +
OrderDetails.item_price" to get a total. This was never considered a
bug by the original creators, but the planned behavior