CODEWITHSUNDEEP
mysql
Richard
Richard

Reputation: 5

Using a join or sub-query to update each row in table MYSQL

I'm trying to update two attributes/ columns in one table, from data in another table created using a subquery. Heres my code.

CREATE TABLE tmpempcnt (SELECT projno, count(*) as ct FROM Pworks group by projno);

UPDATE Proj 
INNER JOIN tmpempcnt ON Proj.projno = tmpempcnt.projno
SET Proj.empcnt = tmpempcnt.ct; 

CREATE TABLE (SELECT projno as p, SUM(hours) as h FROM Pworks GROUP BY projno);

UPDATE Proj
INNER JOIN tmphrstotal ON Proj.projno = tmphrstotal.p
SET Proj.hrstotal = tmphrstotal.h;

Is there a way to do this without creating a table beforehand? Thanks

Upvotes: 0

Views: 33

Answers (1)

P.Salmon
P.Salmon

Reputation: 17655

mysql allows a multitable update https://dev.mysql.com/doc/refman/8.0/en/update.html. In your case 

drop table if exists pworks,proj;

create table proj(projno int,empcnt int, hrstotal int);
create table pworks(projno int,hours int);

insert into proj values(1,null,null);

insert into pworks values
(1,10),(1,10);

update proj 
join (select projno,count(*) cnt,sum(hours) hrs from pworks group by projno) s on s.projno = proj.projno
set   empcnt = cnt,hrstotal = hrs;

may do

Upvotes: 1

Related Questions