125487 125487 - 6 days ago 9
PHP Question

MariaDB on duplicate key update

if the key is duplicated, i want to make qty = (current qty)+(new qty)

https://mariadb.com/kb/en/mariadb/insert-on-duplicate-key-update/

in Mariadb doc, there is only assigning column value case.

CREATE TABLE Cart(
buyer varchar(255) NOT NULL PRIMARY KEY,
productid int(11) NOT NULL PRIMARY KEY,
qty int(11) NOT NULL
);

$addCart_sql = "INSERT INTO Cart (buyer, productid, qty)
VALUES ('$buyer', $productid, $qty)
ON DUPLICATE KEY UPDATE qty = VALUES(qty + $qty)";


i tried

ON DUPLICATE KEY UPDATE qty = VALUES(qty) + VALUES($qty);
ON DUPLICATE KEY UPDATE qty = VALUES(qty) + $qty;


but all cases return error

how can i do??

Answer

try

ON DUPLICATE KEY UPDATE qty = VALUES(qty) + qty