UPDATE with MAX

[kbsc]

I have a Select Query that I have tested and is pulling the results I want, now I need to create an UPDATE statement and I'm not sure how to with a MAX involved in the update. Please help!! I'm not familiar with writing UPDATE stmts. Thanks in advance.

SELECT STATEMENT

SELECT MAX(T.dteTransaction)
FROM tblCase C INNER JOIN tblBankAccount B ON C.intCaseID = B.intCaseID
INNER JOIN tblTransaction T ON B.intAccountID = T.intAccountID
GROUP By C.vchCaseNumber, B.vchAccountNumber
HAVING B.vchAccountNumber IN
(
SELECT BA.vchAccountNumber
FROM tblBankAccount BA LEFT JOIN tblMonthEndBalance M ON BA.vchAccountNumber = M.vchAccountNumber
GROUP BY BA.vchTrustee, BA.intDeleted, BA.dteClosed, BA.mnyBalanceAmount, BA.vchAccountNumber, M.mnyBalance, M.vchCreatedBy
HAVING BA.vchTrustee = 'EHB'
AND BA.intDeleted = 0
AND BA.dteClosed = '1/1/1900'
AND BA.mnyBalanceAmount = 0
AND M.mnyBalance = 0
AND MAX((dateAdd(month,1,M.dteBalance))) < MAX((dateAdd(month,0,M.dteCreated)))
AND M.vchCreatedBy = 'TES'
)
ORDER BY C.vchCaseNumber

MY UPDATE STMT:

UPDATE tblBankAccount
SET
B.dteClosed = MAX(T.dteTransaction)
,B.intDontPostInterest = 1
,B.vchEditedBy = autoBAClose
,B.dteEdited = GetDate()

FROM tblCase C INNER JOIN tblBankAccount B ON C.intCaseID = B.intCaseID
INNER JOIN tblTransaction T ON B.intAccountID = T.intAccountID
GROUP By C.vchCaseNumber, B.vchAccountNumber
HAVING B.vchAccountNumber IN
(
SELECT BA.vchAccountNumber
FROM tblBankAccount BA LEFT JOIN tblMonthEndBalance M ON BA.vchAccountNumber = M.vchAccountNumber
GROUP BY BA.vchTrustee, BA.intDeleted, BA.dteClosed, BA.mnyBalanceAmount, BA.vchAccountNumber, M.mnyBalance, M.vchCreatedBy
HAVING BA.vchTrustee = 'EHB'
AND BA.intDeleted = 0
AND BA.dteClosed = '1/1/1900'
AND BA.mnyBalanceAmount = 0
AND M.mnyBalance = 0
AND MAX((dateAdd(month,1,M.dteBalance))) < MAX((dateAdd(month,0,M.dteCreated)))
AND M.vchCreatedBy = 'TES'
)
ORDER BY C.vchCaseNumber

 

[kbsc]

Ok I've revised my UPDATE query, any input would be really great:

UPDATE tblBankAccount

SET

bank.dteClosed = maxDate.DateClosed
,bank.intDontPostInterest = 1
,bank.vchEditedBy = autoBAClose
,bank.dteEdited = GetDate()

FROM tblBankAccount bank
INNER JOIN (
SELECT C.vchCaseNumber, B.vchAccountNumber, B.intAccountID, MAX(T.dteTransaction) As DateClosed
FROM tblCase C INNER JOIN tblBankAccount B ON C.intCaseID = B.intCaseID
INNER JOIN tblTransaction T ON B.intAccountID = T.intAccountID
GROUP By C.vchCaseNumber, B.vchAccountNumber
HAVING B.vchAccountNumber IN
(
SELECT BA.vchAccountNumber
FROM tblBankAccount BA LEFT JOIN tblMonthEndBalance M ON BA.vchAccountNumber = M.vchAccountNumber
GROUP BY BA.vchTrustee, BA.intDeleted, BA.dteClosed, BA.mnyBalanceAmount, BA.vchAccountNumber, M.mnyBalance, M.vchCreatedBy
HAVING BA.vchTrustee = 'EHB'
AND BA.intDeleted = 0
AND BA.dteClosed = '1/1/1900'
AND BA.mnyBalanceAmount = 0
AND M.mnyBalance = 0
AND MAX((dateAdd(month,1,M.dteBalance))) < MAX((dateAdd(month,0,M.dteCreated)))
AND M.vchCreatedBy = 'TES'
)
) As maxDate ON bank.intAccountID = maxDate.intAccountID