Update counts for each row dependent on id

MySQL & MariaDB Other MySQL® Questions
1

I’ve got the following php code:

$SQL = “select ID from mydb.Domains”;$result = mysql_query($SQL);while(($row=mysql_fetch_assoc($result))) { $DomainID = $row[‘ID’]; // Update this domain’s incoming link count. $SQL = “update mydb.Domains set LinksIn=(select count(ID) from mydb.Links2 where GoingToDomainID=$DomainID), LinksOut=(select count(ID) from mydb.Links2 where ComingFromDomainID=$DomainID) where ID=$DomainID”; mysql_query($SQL);}

I’m wondering if there is a way to perform this in a single query. Playing with the sql a bit I did manage to come up with a select that gets the information:

select Domains.ID, (select count(ID) from mydb.Links2 where GoingToDomainID=Domains.ID) as LinksIn, (select count(ID) from mydb.Links2 where ComingFromDomainID=Domains.ID) as LinksOut from mydb.Domains, mydb.Links2 group by Domains.ID

But I’m not sure where to go from there.

Any advice would be appreciated. Thanks!

G-Man

2

G,

If the Domains and Links2 tables don’t have millions of rows, try one of these:

Here’s a one-line UPDATE.

  1. MySQL allows more than one table in the UPDATE clause
  2. … but keep in mind that ANSI SQL only allows one table in the UPDATE clause
  3. Note the two subqueries are each executed only once

UPDATE Domains, ( SELECT l2in.GoingToDomainID l2ID, count() cIn FROM mydb.Links2 l2in GROUP BY l2in.GoingToDomainID ) L2_In, ( SELECT l2out.ComingFromDomainID l2ID, count() cOut FROM mydb.Links2 l2out GROUP BY l2out.ComingFromDomainID ) L2_OutSET Domains.LinksIn = L2_In.cIn, Domains.LinksOut = L2_Out.cOutWHERE L2_In.l2ID = Domains.ID AND L2_Out.l2ID = Domains.ID

This one-line UPDATE works in MySQL and can be made to work in most ANSI-ish SQL dialects.

  1. Note there’s only one table in the UPDATE clause
  2. BUT, the tradeoff is that the subqueries are each executed once for every updated Domains row

UPDATE Domains, SET LinksIn = ( select count() cIn from mydb.Links2 l2in where l2in.GoingToDomainID =Domains.ID ) L2_In, LinksOut = ( select count() cOut from mydb.Links2 l2out where l2out.ComingFromDomainID=Domains.ID ) L2_Out

Here’s a SELECT version that works in MySQL and can be tweaked for ANSI SQL dialects. (I don’t recall whether the ANSI SQL spec allows a GROUP BY clause in a subquery.)

SELECT Domains.ID L2_In.cIn, L2_Out.cOutFROM mydb.Domains, ( SELECT l2in.GoingToDomainID l2ID, count() cIn FROM mydb.Links2 l2in GROUP BY l2in.GoingToDomainID ) L2_In, ( SELECT l2out.ComingFromDomainID l2ID, count() cOut FROM mydb.Links2 l2out GROUP BY l2out.ComingFromDomainID ) L2_OutWHERE L2_In.l2ID = Domains.ID AND L2_Out.l2ID = Domains.IDORDER BY Domains.ID

There is also a SELECT version that works like the second UPDATE statement, but it’s just too inefficient to use when the previous SELECT will do the same so much more efficiently.

If the Domains and Links2 tables have millions of rows, consider alternatives such as temporary tables at reporting time or incremental aggregation at (insert into Links2) time.

–jim

3

Awesome! Thank you very very much! I’d given up hope that it could be done. That is a very clear and concise example and exactly what i needed )

G-Man