Tuesday, January 31, 2017

MySQL Update example Using group by and Inner Join

MySQL Update example Using group by and Inner Join


 UPDATE tmpbilledsummary AS a 
INNER JOIN
( SELECT
a.practiceCode AS accountCode, b.name AS accountName, COUNT(*) AS counter
FROM
laborder a, practice b
WHERE
a.practiceCode = b.code
AND
a.DOS >= COALESCE(pFromDOS,DOS)
AND
a.DOS <= COALESCE(pToDOS,DOS)
GROUP BY a.practiceCode
) AS b
ON
a.accountCode = b.accountCode
SET a.clientOrders = b.counter;


Available link for download