Mysql UPDATE query SET from the result of another query

Question

Im stuck with this.. I've made a query...

    SELECT ID_Rollo, ID_Rollo_Original
FROM 
    interpretes 
    LEFT JOIN (
        SELECT ID_Rollo_Original, ID_Rollo, count(ID_Rollo) as count
        FROM rollooriginal 
        GROUP BY rollooriginal.ID_Rollo
        HAVING count = 1
    ) rollooriginal ON  interpretes.ID_Programa = rollooriginal.ID_Rollo
WHERE 
interpretes.ID_Prog IS NULL AND ID_Rollo IS NOT NULL

returns me something like this:

ID_Rollo:  ID_Rollo_Original
78 ------- 656
88 ------- 5012

In other hand i have a table interpretes with some cols.

ID_table: ID_Programa: ID_Prog: etc: etc:
1 ------- 78 -------- NULL ------
2 ------- 88 -------- NULL ------
3 ------- 642 ------- 2108 ------
etc....

In some cases ID_Prog is NULL, what i need to do is create query to UPDATE the table interpretes and copy the result of ID_Rollo_Original to ID_Prog if ID_Prog IS NULL..

Notice that ID_Rollo from the query and ID_Programa from interpretes are equals.

EXPECTED

ID_table: ID_rollo: ID_Prog: etc: etc:
1 ------- 78 ------ 656 ------
2 ------- 88 ------ 5012 ------

Something like this...

UPDATE interpretes SET interpretes.ID_Prog = (ID_Rollo_Original) WHERE interpretes.ID_Programa = (RESULT ID_Rollo) AND interpretes.ID_Prog IS NULL

You can check here using temporary table: https://stackoverflow.com/a/68108553/7820853 — Eliud Amukambwa, Jun 24 '21 at 01:31

score 1 · Accepted Answer · answered Jan 05 '18 at 20:18

could be using a join with subquery

UPDATE interpretes  a 
INNER  JOIN (
    SELECT ID_Rollo, ID_Rollo_Original
    FROM interpretes 
    LEFT JOIN (
            SELECT ID_Rollo_Original, ID_Rollo, count(ID_Rollo) as count
            FROM rollooriginal 
            GROUP BY rollooriginal.ID_Rollo
            HAVING count = 1
        ) rollooriginal ON  interpretes.ID_Programa = rollooriginal.ID_Rollo
    WHERE interpretes.ID_Prog IS NULL AND ID_Rollo IS NOT NULL
  ) T on t.ID_ROLLO = a.ID_ROLLO 
  SET a.ID_Prog = t.ID_Rollo_Original
  WHERE a.ID_Prog is NULL

Mysql UPDATE query SET from the result of another query

1 Answers1