UPDATE cAgent ; SET yMonth = dtSum.yMonth ; FROM cAgent ; JOIN ; (SELECT SUM(yAmount) AS yMonth, ; iAgentID, MONTH(dDate) AS nMonth, ; Year(dDate) AS nYear ; FROM cMovs ; GROUP BY iAgentID, nMonth, nYear) dtSum ; ON dtSum.iAgentID = cAgent.iID ; AND dtSum.nMonth = cAgent.nMonth ; AND dtSum.nYear = cAgent.nYear>Lets supose I have a table with every entry in the system:
>CREATE CURSOR cMovs (iid int autoinc, iAgentID int, yAmount y, dDate d) > >RAND(-1) >FOR lnCounter=1 TO 100 > INSERT INTO cMovs (iAgentID, yAmount, dDate) ; > VALUES ((RAND()*10)-1, RAND()*1000000,date(2005,01,01)+ (RAND()*45)) >ENDFOR >>And I have an entity where I would like to get the sumary of each agent by month and year...
>CREATE CURSOR cAgent (iid int autoinc, yMonth y, nMonth int, nYear int) >FOR lnCounter=1 TO 10 > INSERT INTO cAgent (yMonth, nMonth, nYear) ; > VALUES (0,1,2005) >ENDFOR >>I'd like to Update the cAgent cursor with a single UPDATE-SQL...
>UPDATE cAgent ; > SET yMonth = (SELECT SUM(yAmount) ; > FROM cMovs ; > WHERE cMovs.iAgentID = cAgent.iID > AND MONTH(dDate)= cAgent.nMonth ; > AND YEAR(dDate) = cAgent.nYear) ; > WHERE cAgent.nMonth = 1 AND cAgent.Year=2005 >>If I do the above UPDATE-SQL I get the Error 1828 "SQL: Illegal GROUP BY in subquery.".