基于SELECT结果的更新
问题描述:
我想基于自身的子查询更新表。所以我需要子查询的结果作为UPDATE语句中的参数。基于SELECT结果的更新
我没有成功如下尝试:
UPDATE xx
INNER JOIN (
SELECT r.id as id, w.state as state
...
) yy
SET xx.state = yy.state WHERE xx.id = yy.id;
编辑: 全面查询:
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;
答
,如果你有子查询没有特别的计算,并假定它是从不同的表,你可以直接加入表格,
UPDATE table1 a
INNER JOIN table2 b
ON a.id = b.id
SET a.colName = b.value
更新1
这里的查询的简化版本,
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
怎么样加入表'dpcio_request'直接与'dpcio_request_wf'上,而在子查询的最大ID计算? – 2013-02-09 06:41:53
工作,谢谢...! – normalUser 2015-09-01 14:59:18