CODEWITHSUNDEEP
mysql
medo ampir
medo ampir

Reputation: 1910

How to increment with the new value when on duplicate key?

I am using this statement to insert or update on duplicate key.

INSERT INTO `tblrequests` (`request_user_id`, `request_rest_item_id`, `request_amount`) VALUES (select f1,f2,f3 from tbltmb)
 on duplicate key
 update `request_amount`=values(`request_amount`);

but what i need is to update the current original field to be originalvalue + newvalue like this:

INSERT INTO `tblrequests` (`request_user_id`, `request_rest_item_id`, `request_amount`) VALUES (23, 7, 3)
 on duplicate key
 update `request_amount`=values(`old_request_amount` + `new_request_amount`);

is there a way to achieve that?

Upvotes: 0

Views: 97

Answers (2)

geomagas
geomagas

Reputation: 3280

Naively thinking, he syntax can be different, depending on whether you're doing a insert ... select or an insert ... values. But don't make any assumptions until you read up ;)

insert ... select case:

This is easy. Every field has its distinct field name which can be used in the update expression, and mysql is smart enough to figure out what/if to update when multiple rows are being inserted.

INSERT INTO `tblrequests` 
  (`request_user_id`, `request_rest_item_id`, `request_amount`) 
SELECT f1,f2,f3 from tbltmp
 on duplicate key
 update `request_amount`=`request_amount`+`f3`;

insert ... values case:

Well, to insert a single row, and since you know the corresponding value beforehand, it should be staightforward:

INSERT INTO `tblrequests` 
  (`request_user_id`, `request_rest_item_id`, `request_amount`) 
VALUES (23, 7, 3)
 on duplicate key
 update `request_amount`=`request_amount`+3;

See? We only add 3, since it's a constant, known value. But don't get used to that syntax! Consider this:

INSERT INTO `tblrequests` 
  (`request_user_id`, `request_rest_item_id`, `request_amount`) 
VALUES (23, 7, 3),(35,10,23),(2,4,99)

Although the values are known beforehand, the amount we should add is not constant anymore, because it's different for each row inserted.

The general syntax

Luckily, there's a more general syntax to cover all cases: 

on duplicate key
update request_amount=request_amount+values(request_amount);

values(request_amount) simply means "the value I'm trying to put inside the request_amount field, for each row, whatever that corresponds to". This is destionation-oriented, thus independent of whether the source is a select or a series of constant tuples. So the above queries would be rewritten as:

INSERT INTO `tblrequests` 
  (`request_user_id`, `request_rest_item_id`, `request_amount`) 
SELECT f1,f2,f3 from tbltmp
 on duplicate key
 update `request_amount`=`request_amount`+values(`request_amount`);

INSERT INTO `tblrequests` 
  (`request_user_id`, `request_rest_item_id`, `request_amount`) 
VALUES (23, 7, 3)
 on duplicate key
 update `request_amount`=`request_amount`+values(`request_amount`);

INSERT INTO `tblrequests` 
  (`request_user_id`, `request_rest_item_id`, `request_amount`) 
VALUES (23, 7, 3),(35,10,23),(2,4,99)
 on duplicate key
 update `request_amount`=`request_amount`+values(`request_amount`);

Upvotes: 1

M Shahzad Khan
M Shahzad Khan

Reputation: 935

Just add column name + new value. 

INSERT INTO `tblrequests` (`request_user_id`, `request_rest_item_id`, `request_amount`) VALUES (23, 7, 3)
 on duplicate key
 update `request_amount`=values(`request_amount` + 3);

Upvotes: 0

Related Questions