Question

I have a table of this structure:

mysql> desc securities;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| sym             | varchar(19) | NO   | PRI |         |       |
| bqn             | int(11)     | YES  |     | NULL    |       |
| sqn             | int(11)     | YES  |     | NULL    |       |
| tqn             | int(11)     | YES  |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

I am trying to do a select and an update within the same query, so the reason I have chosen

insert into securities (sym, bqn, sqn , tqn) values('ANK', 50,0,1577798) 
on duplicate key update  bqn=bqn+50 , sqn=sqn+0 , tqn=tqn+1577798;

When I ran the above I observed it is in fact changing the values for all the other rows also.

Is this behaviour expected? I am using MySQL Database.

Was it helpful?

Solution

Your fiddle is missing the key, and the INSERT statement in the right panel (where it does not belong in the first place) is using different column names … *sigh*

Define the symbol column as PRIMARY KEY – and use the VALUES() syntax to get the values to add in the ON UPDATE part, so that you don’t have to repeat them every single time:

insert into securities
  (symbol, buyerquan, sellerquan , totaltradedquan)
  values('BANKBARODA', 73, 0, 4290270) 
  on duplicate key update  
    buyerquan=buyerquan+VALUES(buyerquan),
    sellerquan=sellerquan+VALUES(sellerquan),
    totaltradedquan=totaltradedquan+VALUES(totaltradedquan);

Works perfectly fine, result values are as to be expect from the input: http://sqlfiddle.com/#!2/21638f/1

Licensed under: CC-BY-SA with attribution
Not affiliated with StackOverflow
scroll top