jazz jazz - 5 months ago 25
SQL Question

sql insert or update by compairing values

lets say i have a table like this with a lot rows:

id | a | b | c


before every INSERT there should be a check if there is a row with same 'a' and same 'b', if yes don't INSERT but UPDATE 'c' on that row by summing up their c's.

Does anybody know how to realize that in sql? Thanks!

Answer

Here a example with ON DUPLICATE KEY:

You must create a unique key on a and b

ALTER TABLE my_table ADD UNIQUE KEY idx_ab (a,b);

Insert or UPDATE

INSERT INTO my_table (a,b,c) VALUES (1,2,3),(4,5,6)
    ON DUPLICATE KEY UPDATE c=c+VALUES(c);

sample

MariaDB [yourSchema]> select * from my_table;
Empty set (0.00 sec)

MariaDB [yourSchema]> INSERT INTO my_table (a,b,c) VALUES (1,2,3),(4,5,6)     ON DUPLICATE KEY UPDATE c=VALUES(c);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [yourSchema]> select * from my_table;
+----+------+------+------+
| id | a    | b    | c    |
+----+------+------+------+
|  6 |    1 |    2 |    3 |
|  7 |    4 |    5 |    6 |
+----+------+------+------+
2 rows in set (0.00 sec)

MariaDB [yourSchema]> INSERT INTO my_table (a,b,c) VALUES (1,2,99) ON DUPLICATE KEY UPDATE c=VALUES(c);
Query OK, 2 rows affected (0.05 sec)

MariaDB [yourSchema]> select * from my_table;
+----+------+------+------+
| id | a    | b    | c    |
+----+------+------+------+
|  6 |    1 |    2 |   99 |
|  7 |    4 |    5 |    6 |
+----+------+------+------+
2 rows in set (0.00 sec)

MariaDB [yourSchema]>