Mysql query on duplicate key update

Question

I have table Product with fields : id (PK), title (UQ), price, status

I wrote query which should update row if table already has such title

INSERT INTO product (title, price, `status`) 
            VALUES("Black boots", 222.18, 1) 
                ON DUPLICATE KEY UPDATE price = 125.00 AND `status` = 2

That's just example for one product. However, after running this query I am getting message that query was successfuly executed, but price becomes = 0.00 (sometimes 1.00) and status does not change. What is my problem?

Edu C. · Accepted Answer · 2016-02-22 21:01:33Z

4

To update multiple values you need to use the comma operator:

INSERT INTO product (title, price, `status`) 
VALUES("Black boots", 222.18, 1) 
ON DUPLICATE KEY UPDATE price = 125.00, `status` = 2

answered Feb 22, 2016 at 21:01

Edu C.

3932 silver badges8 bronze badges

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

Comments

Bernd Buffen · Accepted Answer · 2016-02-22 21:33:02Z

0

You can also use the argument Values. That allows you to INSERT / UPDATE more then one row

INSERT INTO product (title, price, `status`)
VALUES
 ("Black boots", 222.18, 1),
 ("Green boots", 111.22, 3)
ON DUPLICATE KEY UPDATE price = VALUES(price), `status` = VALUES(status);

answered Feb 22, 2016 at 21:33

Bernd Buffen

15.1k2 gold badges27 silver badges41 bronze badges

Collectives™ on Stack Overflow

Mysql query on duplicate key update

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related