MySQL On Duplicate Key Update

Question

It is only allowing me to update resulting in a total of 2

Heres my Table

CREATE TABLE `cart` (
  `id` int(7) NOT NULL AUTO_INCREMENT,
  `User` int(7) DEFAULT NULL,
  `Product` varchar(100) DEFAULT NULL,
  `Quantity` int(7) DEFAULT NULL,
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `Quantity` (`Quantity`)
 )

Then, my code to insert the data is:

$a = '1'

query2 = "      INSERT INTO CART(User, Product,Quantity)
                VALUES
                ('$id','$model_number','$a')
                 ON DUPLICATE KEY UPDATE Quantity=Quantity+1";

It will work when I Add the data to the database but, upon my second attemp I get this error:

Duplicate entry '2' for key 'Quantity'

dkamins · Accepted Answer · 2011-06-04 01:33:29Z

2

You probably don't want this line:

  UNIQUE KEY `Quantity` (`Quantity`)

That creates a unique constraint on the quantity field, which is why your second insert is failing. I can't think of any reason why you would want that.

P.S. If you remove that line, make sure to remove the comma (,) from the previous line.

answered Jun 4, 2011 at 1:33

dkamins

22.1k7 gold badges58 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ryan Over a year ago

Was the last statement a joke? That is extremely obvious

dkamins Over a year ago

@RPM You would be surprised what's not obvious to many people.

Joshua Martell Over a year ago

Or use remove the UNIQUE part if you still want to do fast lookups by Quantity.

Collectives™ on Stack Overflow

MySQL On Duplicate Key Update

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related