Sql how to count the number of occurrences of the same rows in the database

user_19458660 0
Viewed 36

My data in mySql table:

Cart_id    Product_id    Quantity
 1           1             1

Can i do somehow, that acction: when i add next row with datas: cart_id 1 and product_id 1 and quantity 1 , the new row will be not inserted, but the first row will update quantity to 2?

But when i insert datas: cart_id 1 and product_id 2, the new row will be inserted.

sql-update triggers mysql sql database
1 Answers

Use a Query like this:

INSERT INTO myTable (Cart_id,Product_id,Quantity) VALUES( 1,1,1)
ON DUPLICATE KEY UPDATE Quantity=Quantity+VALUES(Quantity);

Here a Sample:

create a Table:

CREATE TABLE `myTable` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `Cart_id` int(11) DEFAULT NULL,
  `Product_id` int(11) DEFAULT NULL,
  `Quantity` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

add the unique index:

ALTER TABLE `myTable` ADD UNIQUE INDEX (`Cart_id`, `Product_id`);

you can also direct create the index when creating the table

insert rows sample:

MariaDB [bernd]> INSERT INTO myTable (Cart_id,Product_id,Quantity) VALUES( 1,1,1)
    -> ON DUPLICATE KEY UPDATE Quantity=Quantity+VALUES(Quantity);
Query OK, 1 row affected (0.003 sec)

MariaDB [bernd]> SELECT * from myTable;
+----+---------+------------+----------+
| id | Cart_id | Product_id | Quantity |
+----+---------+------------+----------+
|  1 |       1 |          1 |        1 |
+----+---------+------------+----------+
1 row in set (0.000 sec)

MariaDB [bernd]> 
MariaDB [bernd]> INSERT INTO myTable (Cart_id,Product_id,Quantity) VALUES( 1,1,1)
    -> ON DUPLICATE KEY UPDATE Quantity=Quantity+VALUES(Quantity);
Query OK, 2 rows affected (0.001 sec)

MariaDB [bernd]> SELECT * from myTable;
+----+---------+------------+----------+
| id | Cart_id | Product_id | Quantity |
+----+---------+------------+----------+
|  1 |       1 |          1 |        2 |
+----+---------+------------+----------+
1 row in set (0.001 sec)

MariaDB [bernd]> INSERT INTO myTable (Cart_id,Product_id,Quantity) VALUES( 1,1,1)
    -> ON DUPLICATE KEY UPDATE Quantity=Quantity+VALUES(Quantity);;
Query OK, 2 rows affected (0.001 sec)


MariaDB [bernd]> SELECT * from myTable;
+----+---------+------------+----------+
| id | Cart_id | Product_id | Quantity |
+----+---------+------------+----------+
|  1 |       1 |          1 |        3 |
+----+---------+------------+----------+
1 row in set (0.000 sec)

MariaDB [bernd]> INSERT INTO myTable (Cart_id,Product_id,Quantity) VALUES( 1,1,5)
    -> ON DUPLICATE KEY UPDATE Quantity=Quantity+VALUES(Quantity);
Query OK, 2 rows affected (0.002 sec)

MariaDB [bernd]> SELECT * from myTable;
+----+---------+------------+----------+
| id | Cart_id | Product_id | Quantity |
+----+---------+------------+----------+
|  1 |       1 |          1 |        8 |
+----+---------+------------+----------+
1 row in set (0.000 sec)

MariaDB [bernd]>
user_52472790
Related