mysql - 重复键更新，连接表字段

Question

kb_nid是唯一键和c_nodes表；包括 kb_nid、runState、startedTime 字段

我想在表更新之前比较旧表，但出现此错误:

"SQL Error (1054): Unknown column 'cn.runState' in 'field list'"

INSERT INTO c_nodes(kb_nid,runState,startedTime)
    SELECT n.id, n.nState>10 as runState, NULL as startedTime 
    FROM node n 
    LEFT JOIN  c_nodes cn ON n.id =cn.kb_nid
    GROUP BY n.id
    ON DUPLICATE KEY UPDATE startedTime=IF(cn.runState<>VALUES(runState) ,NOW(),cn.startedTime)

我看不出有什么问题。

编辑http://sqlfiddle.com/#!9/8f8a84

感谢您的帮助。

Answer 1

我用子查询修复了它。我猜错误原因是GROUP BY

INSERT INTO c_nodes(kb_nid,runState,startedTime) 
SELECT n.id,runState,startedTime  FROM (
SELECT n.id, n.nState>10 as runState, NULL as startedTime 
FROM node n 
GROUP BY n.id) tt
LEFT JOIN  c_nodes cn ON tt.id =cn.kb_nid
ON DUPLICATE KEY UPDATE startedTime=IF(cn.runState<>VALUES(runState) ,NOW(),cn.startedTime)

引用号:https://stackoverflow.com/a/14533265/3952108