I have table as shown below
ItemAttMapID ItemAttributeID VendorID vItemID Qty
1 4 1 2 60
2 4 2 3 10
3 3 5 5 20
In this table ItemAttMapID
is auto_increment
primary_key
and ItemAttributeID, VendorID, VItemID
is Unique Mapping
.
I used following query
INSERT INTO `client`.`mapping`
(`ItemAttMapID`, `ItemAttributeID`, `VendorID`, `vItemID`, `Qty`)
VALUES
(NULL, 4, '2', '3', '70'),
(NULL, 3, '5', '5', '80')
ON DUPLICATE KEY UPDATE Qty = VALUES(Qty);
It gives me proper result which I want but when I used following query instead of updating the perticular record it inserts the new one.
INSERT INTO `client`.`mapping`
(`ItemAttMapID`, `ItemAttributeID`, `VendorID`, `vItemID`, `Qty`)
VALUES
(NULL, 2, '5', '5', '80')
ON DUPLICATE KEY UPDATE Qty = VALUES(Qty);
and change in table is like this
ItemAttMapID ItemAttributeID VendorID vItemID Qty
1 4 1 2 60
2 4 2 3 10
3 3 5 5 20
4 2 5 5 80
I want the 3rd number row to update but It is inserting a new record to table.
How can I achieve this using ON DUPLICATE KEY UPDATE.
EDIT:
I want to check only, if VendorID
and vItemID
is already present then update the record ( NOT THE ItemAttributeID
) using ON DUPLICATE KEY UPDATE..
Because if I put ItemAttributeID
same along with VendorID
and vItemID
in query then it updates the record otherwise its inserting new record.