首页 文章

INSERT INTO ... SELECT FROM ... on DUPLICATE KEY UPDATE

提问于
浏览
105

我正在进行插入查询,如果已经存在唯一键,则需要将大多数列更新为新值 . 它是这样的:

INSERT INTO lee(exp_id, created_by, 
                location, animal, 
                starttime, endtime, entct, 
                inact, inadur, inadist, 
                smlct, smldur, smldist, 
                larct, lardur, lardist, 
                emptyct, emptydur)
SELECT id, uid, t.location, t.animal, t.starttime, t.endtime, t.entct, 
       t.inact, t.inadur, t.inadist, 
       t.smlct, t.smldur, t.smldist, 
       t.larct, t.lardur, t.lardist, 
       t.emptyct, t.emptydur 
FROM tmp t WHERE uid=x
ON DUPLICATE KEY UPDATE ...; 
//update all fields to values from SELECT, 
//       except for exp_id, created_by, location, animal, 
//       starttime, endtime

我不确定 UPDATE 子句的语法是什么 . 如何引用 SELECT 子句中的当前行?

2 回答

  • 35

    MySQL将假定equals之前的部分引用INSERT INTO子句中指定的列,第二部分引用SELECT列 .

    INSERT INTO lee(exp_id, created_by, location, animal, starttime, endtime, entct, 
                    inact, inadur, inadist, 
                    smlct, smldur, smldist, 
                    larct, lardur, lardist, 
                    emptyct, emptydur)
    SELECT id, uid, t.location, t.animal, t.starttime, t.endtime, t.entct, 
           t.inact, t.inadur, t.inadist, 
           t.smlct, t.smldur, t.smldist, 
           t.larct, t.lardur, t.lardist, 
           t.emptyct, t.emptydur 
    FROM tmp t WHERE uid=x
    ON DUPLICATE KEY UPDATE entct=t.entct, inact=t.inact, ...
    
  • 147

    虽然我已经很晚了但是在看到那些想要使用 INSERT-SELECT 查询和 GROUP BY 条款的人的一些合理问题后,我想出了解决这个问题的方法 .

    进一步回答 Marcus Adams 和会计 GROUP BY ,这就是我用Subqueries in the FROM Clause解决问题的方法

    INSERT INTO lee(exp_id, created_by, location, animal, starttime, endtime, entct, 
                    inact, inadur, inadist, 
                    smlct, smldur, smldist, 
                    larct, lardur, lardist, 
                    emptyct, emptydur)
    SELECT sb.id, uid, sb.location, sb.animal, sb.starttime, sb.endtime, sb.entct, 
           sb.inact, sb.inadur, sb.inadist, 
           sb.smlct, sb.smldur, sb.smldist, 
           sb.larct, sb.lardur, sb.lardist, 
           sb.emptyct, sb.emptydur
    FROM
    (SELECT id, uid, location, animal, starttime, endtime, entct, 
           inact, inadur, inadist, 
           smlct, smldur, smldist, 
           larct, lardur, lardist, 
           emptyct, emptydur 
    FROM tmp WHERE uid=x
    GROUP BY location) as sb
    ON DUPLICATE KEY UPDATE entct=sb.entct, inact=sb.inact, ...
    

相关问题