mysql error near where clause in simple insert statement

Question

INSERT INTO orders_total SET `title`='Discount Coupon:', `text` = '-$40.00', `value` = '40.00' WHERE `orders_id` = '15474' AND `class`='ot_coupon

It gives the following mysql error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE `orders_id` = '15474' AND `class`='ot_coupon'' at line 1

Any idea what I am doing wrong?

The INSERT INTO SET statement does not accept a WHERE clause. — Holger Brandt
– Holger Brandt, Commented Jun 28, 2012 at 12:37

Michael Berkowski · Accepted Answer · 2012-06-28 12:40:30Z

4

INSERT statements are meant to insert new rows, now update existing ones, and a WHERE clause is therefore invalid in an INSERT. You intended to UPDATE.

UPDATE orders_total
SET 
  `title`='Discount Coupon:', 
  `text` = '-$40.00', 
  `value` = '40.00'
WHERE 
  `orders_id` = '15474' 
  AND `class`='ot_coupon'

Edit after comments:

If this was intended to be an insert rather than an update, it cannot have dependencies on conditions like orders_id = 15474. If you are inserting a new row, you need to insert those values as well.

INSERT INTO orders_total (`orders_id`, `class`, `title`, `text`, `value`) VALUES (15474, 'ot_coupon', 'Discount Coupon:', '-$40.00', '40.00');

edited Jun 28, 2012 at 12:40

answered Jun 28, 2012 at 12:33

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

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

1 Comment

Bluemagica Over a year ago

ah silly me, thank you, though I did mean to insert, not update

Collectives™ on Stack Overflow

mysql error near where clause in simple insert statement

1 Answer 1

Edit after comments:

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Edit after comments:

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related