MySQL UPDATE TABLE with subquery does not execute subquery first

Question

I have a query that produces 3 columns (ApplicationID, FamilySize, GrantID). In the initial query FamilySize is a count. The query works, takes about 5 minutes to produce results, and produces expected results. The query is as such:

SELECT t1.ApplicationID, COUNT(*) FamilySize, t1.GrantID
FROM 
(
 SELECT g.ApplicationID, ChildApplicationID `RefID`, g.GrantID
 FROM CONTINUITYCHILD_P `child`
 JOIN uspto.GRANT as g ON child.ApplicationID = g.ApplicationID
 UNION 
 SELECT g2.ApplicationID, ParentApplicationID `RefID`, g2.GrantID
 FROM CONTINUITYPARENT_P `par`
 JOIN uspto.GRANT as g2 ON par.ApplicationID = g2.ApplicationID
) t1
GROUP BY ApplicationID

That initial query works fine. However, I want to use it to update a field in another table Where the GrantID match. I'm using MariaDB but looked at the MySQL 8 documentation since MariaDB and MySQL are supposed to have compatible syntax: https://dev.mysql.com/doc/refman/8.0/en/update.html

I am trying to use the syntax similar to this:

UPDATE t1, t2
SET t1.column = t2.column
WHERE t1.column2 = t2.column2

So that would convert my entire initial query into a table (t2) and the table being updated (t1) would be able to match on a column between t1 and t2.

This is the resulting query:

UPDATE METRICS_G m,
(
SELECT ApplicationID, COUNT(*) FamilySize, GrantID
FROM 
(
 SELECT g.ApplicationID, ChildApplicationID `RefID`, g.GrantID
 FROM CONTINUITYCHILD_P `child`
 JOIN uspto.GRANT as g ON child.ApplicationID = g.ApplicationID
 UNION 
 SELECT g2.ApplicationID, ParentApplicationID `RefID`, g2.GrantID
 FROM CONTINUITYPARENT_P `par`
 JOIN uspto.GRANT as g2 ON par.ApplicationID = g2.ApplicationID
) t1
GROUP BY ApplicationID

) t2
SET m.FamilySize = t2.FamilySize
WHERE m.GrantID = t2.GrantID;

The query gives no errors, and returns in 3ms with 0 rows affected.

This must mean that the sub-query is not being executed since it will normally take a few minutes for that query to return.

What have I done wrong to use the output of a query as a table to match in an UPDATE statement?

Answer 1

You must join the query to the table METRICS_G in the UPDATE statement:

UPDATE METRICS_G m
INNER JOIN (
  SELECT ApplicationID, COUNT(*) FamilySize, GrantID
  FROM (
    SELECT g.ApplicationID, ChildApplicationID `RefID`, g.GrantID
    FROM CONTINUITYCHILD_P `child`
    JOIN uspto.GRANT as g ON child.ApplicationID = g.ApplicationID
    UNION 
    SELECT g2.ApplicationID, ParentApplicationID `RefID`, g2.GrantID
    FROM CONTINUITYPARENT_P `par`
    JOIN uspto.GRANT as g2 ON par.ApplicationID = g2.ApplicationID
  ) t1
  GROUP BY ApplicationID
) t2 ON m.GrantID = t2.GrantID
SET m.FamilySize = t2.FamilySize