Query Help

Hello all, question on a query
How do I turn this into an update query?
SELECT MTE.dbo.GL10110.YEAR1, MTE.dbo.GL10110.PERIODID,
tblEdit.Acct, SUM(MTE.dbo.GL10110.DEBITAMT) - SUM(MTE.dbo.GL10110.CRDTAMNT)
AS Net
FROM tblEdit INNER JOIN
MTE.dbo.GL10110 ON tblEdit.Period =
MTE.dbo.GL10110.PERIODID AND tblEdit.Yr = MTE.dbo.GL10110.YEAR1 INNER JOIN
MTE.dbo.GL00100 ON MTE.dbo.GL10110.ACTINDX =
MTE.dbo.GL00100.ACTINDX AND tblEdit.Acct = MTE.dbo.GL00100.ACTNUMBR_1
GROUP BY MTE.dbo.GL10110.YEAR1, MTE.dbo.GL10110.PERIODID, tblEdit.Acct
ORDER BY tblEdit.Acct
I want it to update the tblEdit table. Just the DeptNet Column. just
as to what appliesHello, Brian
Because you provided such a brief description of the problem, I am only
guessing that you want one of the following (equivalent) queries:
UPDATE tblEdit SET DeptNet=(
SELECT SUM(A.DEBITAMT) - SUM(A.CRDTAMNT)
FROM MTE.dbo.GL10110 A
INNER JOIN MTE.dbo.GL00100 B
ON A.ACTINDX = B.ACTINDX
WHERE tblEdit.Period = A.PERIODID
AND tblEdit.Yr = A.YEAR1
AND tblEdit.Acct = B.ACTNUMBR_1
)
WHERE EXISTS (
SELECT * FROM MTE.dbo.GL10110 A
INNER JOIN MTE.dbo.GL00100 B
ON A.ACTINDX = B.ACTINDX
WHERE tblEdit.Period = A.PERIODID
AND tblEdit.Yr = A.YEAR1
AND tblEdit.Acct = B.ACTNUMBR_1
)
UPDATE tblEdit SET DeptNet=Net
FROM tblEdit INNER JOIN (
SELECT A.YEAR1, A.PERIODID, B.ACTNUMBR_1,
SUM(A.DEBITAMT) - SUM(A.CRDTAMNT) AS Net
FROM MTE.dbo.GL10110 A
INNER JOIN MTE.dbo.GL00100 B
ON A.ACTINDX = B.ACTINDX
GROUP BY A.YEAR1, A.PERIODID, B.ACTNUMBR_1
) x
ON tblEdit.Period = X.PERIODID
AND tblEdit.Yr = X.YEAR1
AND tblEdit.Acct = X.ACTNUMBR_1
The queries are untested, because you did not provide DDL, sample data
and expected results. See: http://www.aspfaq.com/etiquette.asp?id=5006
I assume that (Period, Yr, Acct) is a unique key in tblEdit.
The first query uses ANSI syntax; the second query uses T-SQL syntax.
Razvan