He creado 3 tablas: item , shop y stock . Además de un procedimiento almacenado llamado inserting que se inserta en la tabla de shop con un artículo dado de la tabla de item
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); La primera inserción funciona, pero en la segunda inserción, cuando llena la tabla de stock , me da columnas adicionales, lo que no esperaba.
He intentado usar REPLACE INTO y ON DUPLICATE KEY UPDATE para obtener resultados únicos, pero los resultados son los siguientes:
SELECT * FROM `stock`; +------+-------+ | ITEM | TOTAL | +------+-------+ | 1 | 5 | | 1 | 9 | +------+-------+lo que estoy tratando de lograr es agrupar la columna ARTÍCULO y resumir el TOTAL en una sola fila.
¿Qué estoy haciendo mal aquí, o faltando en la consulta?
Gracias.
Para que la sintaxis de on duplicate key funcione como se esperaba, necesita una restricción de clave principal o única en la tabla de destino, para que la base de datos pueda identificar las filas "duplicadas". Lo mismo ocurre con la sintaxis REPLACE .
Pero su tabla stock no tiene una clave principal. Considere el siguiente DDL en su lugar:
CREATE TABLE stock( item int(11) primary key, total int(11) not null ); Nota al margen: no es necesario reasignar el item de columna en la cláusula on duplicate key , ya que es lo que se usa para identificar el conflicto en primer lugar. Esto es lo suficientemente bueno:
INSERT INTO STOCK(item, total) SELECT s_item, SUM(s_qty) FROM shop GROUP BY s_item ON DUPLICATE KEY UPDATE total = VALUES(total);Si ejecuta esto una vez, debería funcionar como esperaba. Pero las ejecuciones posteriores pueden traer ITEM duplicado debido a lo que dijo @gmb. La tabla debe tener un UNIQUE index o PRIMARY KEY . Ver más detalles aquí
https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html