The proprietary syntax does not work all the time:
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);
SELECT * FROM Orders;
UPDATE Orders
SET Orders.some_col = OrderDetails.item_price
FROM Orders
INNER JOIN
OrderDetails
ON Orders.order_nbr = OrderDetails.order_nbr;
results -- see item #1; last physical value
1 205.00 - where is the $500.00?
2 490.95
3 480.00
--repeat with new physical ordering
DELETE FROM OrderDetails;
DELETE FROM Orders;
DROP INDEX OrderDetails.foobar;
-- index will change the execution plan
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);
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
2 490.95
3 480.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?
This would not have happened with the ANSI syntax. That's the point
that I am trying to make. The ANSI equivalent of the incorrect query
above is
UPDATE Orders -- no alias allowed!
SET some_col
= (SELECT item_price
FROM OrderDetails
WHERE OrderDetails.order_nbr = Orders.order_nbr)
WHERE EXISTS
(SELECT *
FROM OrderDetails
WHERE OrderDetails.order_nbr = Orders.order_nbr);
This will of course result in an error, and even the most junior of
junior programmers will eventually figure out (probably by asking a
senior) what's wrong. At that point, either the query is corrected to
match the request, or a note is sent back to management asking for a
clarification of the ambiguity in the request.
Bottom line: UPDATE FROM *can* be safely used - but only if you are
FOREVER certain that no single row in the target table can EVER be
joined to more than one row in the source table(s); FOREVER in the
ENTIRE LIFETIME of the application; FOREVER across all programmers yet
to come.
I like to err on the safe side, I do not bet only an endless stream of
100% perfect programmers. You correctly guessed the row constructor
syntax that is standard in ANSI/ISO SQL, by the way.
|
