I don’t know why, but I am always forgetting the correct syntax for executing an UPDATE statement that uses a another table. So, I decided to post the syntax right here, if it is helpful, great!
The TSQL below uses an INNER JOIN to update a table based on the results from the joined table.
UPDATE c SET c.IsActive=gl.active FROM #CheckTbl c INNER JOIN PAYSO.dbo.GL00100 gl ON (c.GLAccount=RTRIM(gl.ACTNUMBR_1) + '-' + RTRIM(gl.ACTNUMBR_2) + '-' + RTRIM(gl.ACTNUMBR_3) + '-' + RTRIM(gl.ACTNUMBR_4) + '-' + RTRIM(gl.ACTNUMBR_5))
The next statement is another way of joining 2 tables, but still accomplishing the same task.
UPDATE c SET c.IsFiscalClosed=sy.Closed FROM #CheckTbl c, PAYSO.dbo.SY40100 sy WHERE sy.SERIES=2 AND RTRIM(sy.ODESCTN)='Bank Transaction Entry' AND c.checkDate BETWEEN sy.PERIODDT AND sy.PERDENDT