Reputation: 10395
I'm trying to UPDATE a table based on a sub-query on itself. So I need sub-query's result as parameters in UPDATE statement.
I tried below with no success:
UPDATE xx
INNER JOIN (
SELECT r.id as id, w.state as state
...
) yy
SET xx.state = yy.state WHERE xx.id = yy.id;
EDIT: Full query:
UPDATE dpcio_request xx
INNER JOIN (
SELECT r.id as id, w.state as state
FROM dpcio_request r
JOIN dpcio_request_wf w
ON (w.dpcio_request_id = r.id)
WHERE w.id IN ( SELECT MAX(id)
FROM `dpcio_request_wf`
GROUP BY dpcio_request_id )
) r2 ON r1.id = r2.id
) yy ON yy.id = xx.id
SET xx.state = yy.state;
Upvotes: 1
Views: 4643
Reputation: 263943
if you have no special calculation on the subquery and assuming that it is from a different table, you can directly join the tables,
UPDATE table1 a
INNER JOIN table2 b
ON a.id = b.id
SET a.colName = b.value
UPDATE 1
here's a simplified version of the query,
UPDATE dpcio_request a
INNER JOIN dpcio_request_wf b
ON a.id = b.dpcio_request_wf
INNER JOIN
(
SELECT dpcio_request_id, MAX(id) max_ID
FROM dpcio_request_wf
GROUP BY dpcio_request_id
) c ON b.id = c.max_ID AND
b.dpcio_request_id = c.dpcio_request_id
SET a.state = b.state
Upvotes: 7