user6305775 user6305775 - 5 months ago 14
SQL Question

ON Duplicate Key update doesn't work

I have used the following code to Insert/update MySQL table but its not doing anything when duplicate record exists I used

ON Duplicate Key update
. the code works great to insert but i want to update if description is differ from the source so i added this
ON DUPLICATE KEY UPDATE PURCHASE_DESCRIPTION = VALUES ('$pdesc')"
but it not inserting also not updating

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 is the primary Key

Answer

Your SQL is incorrect. Take a look:

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}' 
");

Another option is UPDATE PURCHASE_DESCRIPTION = VALUES(PURCHASE_DESCRIPTION)

You can read more here: http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

Comments