php - ON 重复 key 更新不起作用

Question

我使用了以下代码来插入/更新 MySQL 表，但当存在重复记录时它不会执行任何操作，我使用了ON Duplicate Key update。该代码非常适合插入，但如果描述与源不同，我想更新，所以我添加了这个 ON DUPLICATE KEY UPDATE PURCHASE_DESCRIPTION = VALUES ('$pdesc')" 但它不插入也不更新

mysqli_query($con,
    "INSERT INTO  table_name
    (STOCK_NO, PURCHASE_DESCRIPTION, SALES_DESCRIPTION, itemId, itype, ITEM_DESCRIPTION, uOfM, uConvFact, poUOfM, lead, suplId, suplProdCode, minLvl, maxLvl, ordLvl, ordQty, unitWgt, sales, bomRev,  makebuy) VALUES
    ('{$itemid}', '{$pdesc}', '{$sdesc}', '{$itemId}', '{$itype}', '{$itemdsc}', '{$uOfM}', '{$uConvFact}', '{$poUOfM}', '{$lead}', '{$supplId}', '{$suplProdCode}', '{$minLvl}', '{$maxLvl}', '{$ordLvl}', '{$ordQty}', '{$unitWgt}', '{$sales}', '{$bomRev}', '{$makebuy}')
    ON DUPLICATE KEY UPDATE PURCHASE_DESCRIPTION = VALUES ('$pdesc')"
);

//STOCK_NO为主键

Answer 1

您的 SQL 不正确。看看:

mysqli_query($con,
"INSERT INTO  table_name
(STOCK_NO, PURCHASE_DESCRIPTION, SALES_DESCRIPTION, itemId, itype, ITEM_DESCRIPTION, uOfM, uConvFact, poUOfM, lead, suplId, suplProdCode, minLvl, maxLvl, ordLvl, ordQty, unitWgt, sales, bomRev,  makebuy) VALUES
('{$itemid}', '{$pdesc}', '{$sdesc}', '{$itemId}', '{$itype}', '{$itemdsc}', '{$uOfM}', '{$uConvFact}', '{$poUOfM}', '{$lead}', '{$supplId}', '{$suplProdCode}', '{$minLvl}', '{$maxLvl}', '{$ordLvl}', '{$ordQty}', '{$unitWgt}', '{$sales}', '{$bomRev}', '{$makebuy}')
ON DUPLICATE KEY 

-- HERE --
UPDATE PURCHASE_DESCRIPTION = '{$pdesc}' 
");

另一个选项是UPDATE PURCHASE_DESCRIPTION = VALUES(PURCHASE_DESCRIPTION)

您可以在这里阅读更多信息:http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html