mysql - how to combine two rows together if they have the same values-CodePudding

So I have a form in which I add some details. Now, let's say I submit the form with the following details:

id_s: 3
id_m: 1
quantity: 5

It's all good, the information is stored in the table on a row. (see below)

Next, if I submit the form with the following:

id_s: 3
id_m: 1
quantity: 3

the table will contain two rows with same values instead of one merged with the information (see below)

What condition should I put in order to merge them into one row so it's gonna be the total of quantity?

CodePudding user response：

If the first two columns are unique, which is what I would expect from your description, then you can do this:

mysql> create table mytable  ( id_substatie int, id_medicament int, cantitate int );

mysql> alter table mytable add primary key (id_substatie, id_medicament);

mysql> insert into mytable values (3,1,5);

mysql> insert into mytable values (3,1,3) 
       on duplicate key update cantitate=cantitate values(cantitate);

mysql> select * from mytable;
 -------------- --------------- ----------- 
| id_substatie | id_medicament | cantitate |
 -------------- --------------- ----------- 
|            3 |             1 |         8 |
 -------------- --------------- ----------- 
1 row in set (0.00 sec)

The expression cantitate values(cantitate) means the old value of that column plus the new value you tried to insert.

CodePudding user response：

You could basically use a trigger for this before every insert

   Create trigger t_sample on
   sample before insert
   For each row
   Begin
   Update sample s set 
   s.quantity=:OLD.quantity :NEW.quantity 
   where s.id_s=:NEW.id_s and s.id_m=:NEW.id_m
    and s.quantity <>:NEW.quantity 
   ;
   End;
   /