CODEWITHSUNDEEP
mysql
Fusseldieb
Fusseldieb

Reputation: 1374

ON DUPLICATE KEY UPDATE won't work

I'm trying like hours to make a simple MySQL command and it won't work. I have a database where package IDs for Apps are stored, like 'com.android.package'. They are stored like so:

| ID | PackageID           | PackageDesc       |
| 1  | com.android.package | This is a package |
| 2  | com.android.test2pa | This is package 2 |

And so on...

Now I want to insert a new entry, if 'com.android.package' don't exist and if it exists, I want to do nothing...

I've tried following:

 INSERT INTO Packages (PackageID) VALUES ('com.android.package') ON DUPLICATE KEY UPDATE PackageID=VALUES(PackageID)

But it still creates new entries, like that:

| ID | PackageID           | PackageDesc       |
| 3  | com.android.package |                   |
| 4  | com.android.package |                   |
| 5  | com.android.package |                   |

I don't know where's my error.

Upvotes: 0

Views: 265

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

A proposed Packages table schema for ON DUPLICATE KEY UPDATE to work as expected is the following:

CREATE TABLE Packages (
   `ID` INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
   `PackageID` VARCHAR(30) NOT NULL,
   `PackageDesc` VARCHAR (100),
   UNIQUE(`PackageID`)
);

After this statement is executed:

INSERT INTO Packages (PackageID, PackageDesc) VALUES 
('com.android.package', 'This is a package') 
ON DUPLICATE KEY UPDATE PackageID=VALUES(PackageID);

Packages contains the following row:

# ID, PackageID,            PackageDesc
'1', 'com.android.package', 'This is a package'

If you now execute the following statement:

INSERT INTO Packages (PackageID, PackageDesc) VALUES 
('com.android.package', 'This is a package2') 
ON DUPLICATE KEY UPDATE PackageID=VALUES(PackageID), PackageDesc=VALUES(PackageDesc);

Packages contains:

# ID, PackageID,            PackageDesc
'1', 'com.android.package', 'This is a package2'

This means an UPDATE was performed by the latter INSERT INTO.

Demo here

Upvotes: 2

Related Questions