Cristian25
Cristian25

Reputation: 109

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

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) proof1

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) proof2

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

Upvotes: 1

Views: 815

Answers (2)

Himanshu
Himanshu

Reputation: 3970

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;
   /
    

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562260

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.

Upvotes: 1

Related Questions