本文介紹了在帶有SUM或-MYSQL的重復(fù)鍵更新時(shí)插入的處理方法，對(duì)大家解決問題具有一定的參考價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)吧！
問題描述
我非常努力地尋找答案，但沒有找到
我有這張表
我正在使用這個(gè)查詢來更新它(這個(gè)查詢是必須的，因?yàn)槲矣衼碜郧岸说膭?dòng)態(tài)輸入，所以我可以更新多行)
INSERT INTO main_inventory(name,sellingPrice,purchasePrice,averagePrice,totalQuantity) VALUES("test1",20,5,27.5,23),VALUES("test2",20,5,27.5,50)
ON DUPLICATE KEY UPDATE name = VALUES(name),sellingPrice = VALUES(sellingPrice),purchasePrice = VALUES(purchasePrice),averagePrice = VALUES(averagePrice),totalQuantity = VALUES(totalQuantity)
問題是，如果我運(yùn)行上面的查詢。應(yīng)該更新該表，但我希望添加totalQuantity列，因此Test1的值應(yīng)該是46，而Test2的值應(yīng)該是73。我不知道該怎么辦
名稱列是唯一的，并且是鍵
我正在使用NodeJS進(jìn)行查詢
let query4 = `INSERT INTO main_inventory(name,sellingPrice,purchasePrice,averagePrice,totalQuantity) VALUES`
for(let x=0;x<main_items.length;x++){
if(x+1 == main_items.length){
query4 = query4+`("${main_items[x].item}",${main_items[x].sellingPrice},${main_items[x].purchasePrice},${main_items[x].averagePrice},${main_items[x].quantity})
ON DUPLICATE KEY UPDATE name = VALUES(name),sellingPrice = VALUES(sellingPrice),purchasePrice = VALUES(purchasePrice),averagePrice = VALUES(averagePrice),totalQuantity = VALUES(totalQuantity)`
}else{
query4 = query4+`("${main_items[x].item}",${main_items[x].sellingPrice},${main_items[x].purchasePrice},${main_items[x].averagePrice},${main_items[x].quantity}),`
}
}
推薦答案
您可以引用您在查詢的UPDATE部分插入的列的當(dāng)前值，如：
INSERT INTO main_inventory(name, sellingPrice, purchasePrice, averagePrice, totalQuantity)
VALUES ('test1', 20, 5, 27.5, 23), ('test2', 20, 5, 27.5, 50)
ON DUPLICATE KEY UPDATE
name = VALUES(name),
sellingPrice = VALUES(sellingPrice),
purchasePrice = VALUES(purchasePrice),
averagePrice = VALUES(averagePrice),
totalQuantity = totalQuantity + VALUES(totalQuantity) -- add to the original value
這篇關(guān)于在帶有SUM或-MYSQL的重復(fù)鍵更新時(shí)插入的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，
