Is UPDATE FROM still not in the ANSI standard?

Question

This article fascinated me it claims that FROM clauses in UPDATE are not part of the ANSI standard. Unfortunately, it is very old and I don't care to buy a copy of the latest ANSI standard for SQL. Is it still the case that FROM clauses in UPDATE are not part of the ANSI standard?

Answer 1

You have to buy the document about ISO SQL Standard at ISO.org... And you will see that UPDATE <target_table> FROM ... is not a part of the SQL standard because it is matematically false !

For the case of UPDATE and taking into account the cardinalities involved, it is possible that the update is ambiguous because it is arbitrary. Indeed, if the columns contained in the value expressions to be updated come from joined table(s) whose cardinality is greater than 1 for each row to be updated, then the assigned value will be any of the values resulting from the join!*

As an example, let's see what such an UPDATE JOIN gives with the following tables:

xemple 27 – Mise à jour UPDATE avec jointure :

CREATE TABLE dbo.T_PERSONNE_PHYSIQUE_PSP
(PSP_ID               INT PRIMARY KEY,
 PSP_NOM_NAISSANCE    VARCHAR(64) NOT NULL,
 PSP_NOM_MARITAL      VARCHAR(64),
 PSP_PRENOM_USUEL     VARCHAR(32),
 PSP_DATE_NAISSANCE   DATE NOT NULL);

CREATE TABLE dbo.T_PRENOM_PRN
(PRN_ID      INT PRIMARY KEY,
 PRN_PRENOM  VARCHAR(32) NOT NULL UNIQUE);

CREATE TABLE dbo.T_PERSONNE_PHYSIQUE_PRENOM_PPP
(PPP_ID      INT IDENTITY PRIMARY KEY,
 PSP_ID      INT NOT NULL REFERENCES dbo.T_PERSONNE_PHYSIQUE_PSP (PSP_ID),
 PRN_ID      INT NOT NULL REFERENCES dbo.T_PRENOM_PRN (PRN_ID),
 PPP_ORDRE   TINYINT NOT NULL,
 UNIQUE (PSP_ID, PPP_ORDRE));

Three tables : persons, first names and person's first usual name

INSERT INTO dbo.T_PRENOM_PRN VALUES 
(1, 'Georges'), (2, 'Charles'), (3, 'André'), (4, 'Jean'), 
(5, 'Marie'), (6, 'Joseph'), (7, 'Benjamin'), (8, 'Raymond'),
(9, 'Marc'), (10, 'Paul'), (11, 'Jacques'), (12, 'Simone');

INSERT INTO dbo.T_PERSONNE_PHYSIQUE_PSP VALUES
(99, 'DE GAULLE', NULL, NULL, '1890-11-22'), -- Charles, André, Joseph, Marie
(98, 'CLEMENCEAU', NULL, NULL, '1841-09-28'), -- Benjamin, Georges
(97, 'POMPIDOU', NULL, NULL, '1911-07-05'); --Georges, Jean-Raymond

INSERT INTO dbo.T_PERSONNE_PHYSIQUE_PRENOM_PPP VALUES
(99, 2, 1), (99, 3, 2),  (99, 6, 3), (99, 5, 4),
(98, 7, 1), (98, 1, 2),
(97, 1, 1), (97, 4, 2), (97, 8, 3);

Now we want to set the usual first name (prenom) in the table "T_PERSONNE_PHYSIQUE_PSP"

The following query seems to give the answer :

UPDATE T
SET    PSP_PRENOM_USUEL = P.PRN_PRENOM
FROM   dbo.T_PERSONNE_PHYSIQUE_PSP AS T 
       JOIN dbo.T_PERSONNE_PHYSIQUE_PRENOM_PPP AS PP
          ON T.PSP_ID = PP.PSP_ID
       JOIN dbo.T_PRENOM_PRN AS P
          ON PP.PRN_ID = P.PRN_ID;

The result is:

PSP_ID      PSP_NOM_NAISSANCE   PSP_PRENOM_USUEL
----------- ------------------- ----------------
97          POMPIDOU            Georges
98          CLEMENCEAU          Benjamin
99          DE GAULLE           Charles

Which is the first "first" name in the first name list..

If we add an index such as :

CREATE INDEX X_PPP_PRN_PSP 
   ON T_PERSONNE_PHYSIQUE_PRENOM_PPP (PSP_ID, PRN_ID);

The new result is:

PSP_ID      PSP_NOM_NAISSANCE   PSP_PRENOM_USUEL
----------- ------------------- ------------------
97          POMPIDOU            Georges
98          CLEMENCEAU          Georges
99          DE GAULLE           Charles

Which is false for CLEMENCEAU...

Also you can use UPDATE/JOIN only if you are sure that there will never be any ambiguity on the values to update.