0

I want to update the column value in multi-rows insert SQL.
For example:

INSERT INTO `product_price` (`product_id`, `store_id`,`price`) 
VALUES (20001, 1, `price`+10),
(4442, 1, `price`-5),
(2442, 1, `price`*1.1),
(3002, 1, 399),
ON DUPLICATE KEY UPDATE `price` = VALUES(`price`);

Where you can see, I want to update the price by some constant value or by some self updates (+, -, *, /).

But it doesn't work in case of INSERT (works fine in UPDATE statement).

How to make it workable in INSERT statement.
FYI, I am using MySQL 5.7.

  • No shortcut like you describe. – Rick James Nov 5 '19 at 22:48
1

For an update, you are changing an existing column value in a number of existing rows, 'select'ed based on the "where" clause you specify:

update product_price 
set price = price + 10 
where product_id = 4442 ; 

The query locates any rows with product_id 4442 and then, for each one, extracts the current value of price, adds 10 to it, and stores that result back into each record.

For an insert, you are creating a completely new record which, by definition, cannot contain any values (until after it's been created):

insert into product_price  
values ( 2442, 1, price * 1.1 )

So; what value does price represent in this query?

Before this statement executes, there is no row with product_id 2442.
Therefore, there is no price value (in that non-existent row) for the query to retrieve and modify.

| improve this answer | |
  • So there's no other way round than using UPDATE statement? – MagePsycho Nov 17 '19 at 12:39
  • I fail to see what you're trying to achieve here. If you want to change an existing value in a record then you have to update it. There's no way "around" that. If you want to create a whole new record containing a given value, then you insert that record, including that value. For an insert, you have to know what that value is or have some way of calculating it from other records that already exists. – Phill W. Nov 18 '19 at 8:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.