klaus klaus - 7 months ago 11
SQL Question

Add row if id not exist else add to value in a cell

I have a Mysql Table named

Anonymat_Table
(two collumns
Anonymat
and
Moyenne
). I need to add a row if
Anonymat
(
VarChar(45)
) dosn't exist. Else add value to cell
Moyenne
.

+----------+-----------+
| Anonymat | Moyenne |
+----------+-----------+
| EzBn142d | 12.00 |
+----------+-----------+


I'm building a WPF Project, I have a forme with tow textboxs ana a button so if I click the button and write in the textboxes (EzBn142d and 2.00) it change the table to

+----------+-----------+
| Anonymat | Moyenne |
+----------+-----------+
| EzBn142d | 14.00 |
+----------+-----------+


And if I click the button after I enter in the textboxes (Nbdjm20DV and 4.00) it add a row in the table and will appear like this

+-----------+-----------+
| Anonymat | Moyenne |
+-----------+-----------+
| EzBn142d | 14.00 |
+-----------+-----------+
| Nbdjm20DV | 4.00 |
+-----------+-----------+

Answer

You can use ON DUPLICATE KEY. So the Primary key or a uniqe key must define on FIELD Anonymat.

sample

INSERT INTO Anonymat_Table (Anonymat,Moyenne) 
VALUE('bernd' , 13.99)
ON DUPLICATE KEY UPDATE  Moyenne = Moyenne + VALUES(Moyenne);

it also works with multirows like this

INSERT INTO Anonymat_Table (Anonymat,Moyenne) 
VALUE
('bernd' , 13.99),
('tom' , 1.99),
('mark' , 3.95),
('anna' , 0.95)
ON DUPLICATE KEY UPDATE  Moyenne = Moyenne + VALUES(Moyenne);
Comments