Update field in table with result of select from other table in mySQL Code Answer

Hello Developer, Hope you guys are doing great. Today at Tutorial Guruji Official website, we are sharing the answer of Update field in table with result of select from other table in mySQL without wasting too much if your time.

The question is published on by Tutorial Guruji team.

I have a follow-up question to sum of counts of different columns of same table I want to update a field (Z) in another table (B), which also has ‘ID’ with the results (A.COUNT_TOTAL) of the select query:

SELECT A.ID, SUM(A.COUNTS) AS COUNT_TOTAL
FROM
(
SELECT X AS ID, COUNT(*) AS COUNTS FROM TABLE1 GROUP BY X
UNION ALL
SELECT Y AS ID, COUNT(*) AS COUNTS FROM TABLE1 GROUP BY Y
) A
GROUP BY A.ID
ORDER BY A.ID;`

I tried following statement, (but get a vague error message):

 UPDATE B
    INNER JOIN (
    SELECT A.ID, SUM(A.COUNTS) AS COUNT_TOTAL
    FROM
    (
    SELECT X AS ID, COUNT(*) AS COUNTS FROM TABLE1 GROUP BY X
    UNION ALL
    SELECT Y AS ID, COUNT(*) AS COUNTS FROM TABLE1 GROUP BY Y
    ) A
    GROUP BY A.ID) as A on B.ID = A.ID
    SET B.Z = A.COUNT_TOTAL

Answer

You have ambiguos (and wrong reference to alias A .. you are using two times the same name for different alias ) Try using a differnent alias for different subquery eg:

  UPDATE B
  INNER JOIN (
  SELECT A.ID, SUM(A.COUNTS) AS COUNT_TOTAL
  FROM
  (
  SELECT X AS ID, COUNT(*) AS COUNTS FROM TABLE1 GROUP BY X
  UNION ALL
  SELECT Y AS ID, COUNT(*) AS COUNTS FROM TABLE1 GROUP BY Y
  ) A
  GROUP BY A.ID)  T on B.ID = T.ID
  SET B.Z = T.COUNT_TOTAL
We are here to answer your question about Update field in table with result of select from other table in mySQL - If you find the proper solution, please don't forgot to share this with your team members.

Related Posts

Tutorial Guruji