CODEWITHSUNDEEP
mysqlstored-procedures
Sarah Eddy
Sarah Eddy

Reputation: 11

Why is my procedure populating separate rows?

My stored procedure is populating different rows and I need it to populate a single row. I have tried adding in some where clauses but that breaks it. Any help is appreciated!

 DELIMITER //
CREATE PROCEDURE Populate()
BEGIN
    insert into log(NoEmp) (select count(empid) from emp);
    insert into log(NoDept) (select count(deptid) from dept);
    insert into log(LocReg1) (select count(regionid) from region where regionid=1);
    insert into log(LocReg2) (select count(regionid) from region where regionid=2);
    insert into log(LocReg3) (select count(regionid) from region where regionid=3);
    insert into log(TotSales) (select sum(salesamt) from sales);
    insert into log(AvgSaleMo) (select TotSales/(24) from log);
    insert into log(AvgSaleYr) (select TotSales/(2) from log);
    insert into log(logdate) (select NOW());
    
END //
DELIMITER ;

this is my current output

Upvotes: 1

Views: 24

Answers (1)

Barmar
Barmar

Reputation: 781098

Every time you INSERT it creates a new row (unless you use the ON DUPLICATE KEY UPDATE clause).

If you want to insert a single row, use a single INSERT with multiple columns and values.

INSERT INTO log (NoEmp, NoDept, LocReg1, LocReg2, LocReg3, TotSales, AvgSaleMo, AvgSaleYr, logdate)
VALUES (
    (select count(empid) from emp),
    (select count(deptid) from dept),
    (select count(regionid) from region where regionid=1),
    (select count(regionid) from region where regionid=2),
    (select count(regionid) from region where regionid=3),
    (select sum(salesamt) from sales),
    (select sum(salesamt)/(24) from sales),
    (select sum(salesamt)/(2) from sales),
    NOW()
);

Upvotes: 1

Related Questions