0

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.

Pratik
  • 1,531
  • 3
  • 25
  • 57

0 Answers0