I have created 3 tables: item, shop and stock. Plus a stored procedure called inserting
which inserts to the shop table with a given item from the item table
CREATE TABLE item(
i_id int(11) auto_increment,
i_name varchar(255) not null,
primary key(i_id));
CREATE TABLE shop(
s_id int(11) auto_increment,
s_name varchar(255) not null,
s_item int(11) not null,
s_qty int(11) not null,
primary key(s_id),
foreign key(s_item) references item(i_id)
);
CREATE TABLE stock(
item int(11) not null,
total int(11) not null
);
CREATE PROCEDURE inserting (
IN shop_name varchar(225),
IN shop_item int(11),
IN shop_qty int(11)
)
BEGIN
INSERT INTO shop(s_name, s_item, s_qty)
VALUES
(shop_name, shop_item, shop_qty);
INSERT INTO STOCK(item, total)
SELECT s_item, SUM(s_qty) FROM shop GROUP BY s_item
ON DUPLICATE KEY UPDATE
item = VALUES(item),
total = VALUES(total);
The first insert works, but on the second insert when it populates the stock table it gives me extra columns, which i'm not expecting.
I have tried using REPLACE INTO and ON DUPLICATE KEY UPDATE to get single results, still the results comes as the following:
SELECT * FROM `stock`;
+------+-------+
| ITEM | TOTAL |
+------+-------+
| 1 | 5 |
| 1 | 9 |
+------+-------+
what I am trying to achieve is, group the ITEM column, and sum up the TOTAL to a single row.
what am I doing wrong here, or missing from the query?
thanks.
For the on duplicate key syntax to work as expected, you need a unique or primary key constraint on the target table, so the database can identify the "duplicate" rows. Same goes for the REPLACE syntax.
But your stock table does not have a primary key. Consider the following DDL instead:
CREATE TABLE stock(
item int(11) primary key,
total int(11) not null
);
Side note: there is no need to reassign column item in the on duplicate key clause, since it's what is used to identify the conflict in the first place. This is good enough:
INSERT INTO STOCK(item, total)
SELECT s_item, SUM(s_qty) FROM shop GROUP BY s_item
ON DUPLICATE KEY UPDATE total = VALUES(total);
If you run this one time, it should work as you expected. But subsequent runs may bring duplicate ITEM because of what @gmb said. The table must have a UNIQUE index or PRIMARY KEY. See more details here
https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html