Three problems with this query:
- If there are no results for the correlated subquery, it returns NULL, which can't be added to PTAmount.
- If there are multiple results for the subquery, it will also fail to add since only a single value can be returned from a subquery in parenthesis.
- You can't alias an UPDATE table next to the UPDATE keyword. You need a FROM clause, after the SET, to give it the "ptm" alias.
Try this instead:
UPDATE PayrollTotals SET PTAmount = PTAmount +
(SELECT SUM(pts.PTAmount) FROM PayrollTotals pts WHERE
pts.PTACID = 38
AND pts.PTAmount > 0
AND pts.PTEmpID = ptm.PTEmpID)
FROM
PayrollTotals AS ptm
WHERE
PTACID = 42
The SUM() will ensure that you get at least a 0 result from the subquery, and if there are multiple results, it will sum them before adding them to ptm.PTAmount.
Also, you don't really need the table alias. Since the subquery's PayrollTotals is aliased as pts, you can refer to the updated table directly by its name:
UPDATE PayrollTotals SET PTAmount = PTAmount +
(SELECT SUM(pts.PTAmount) FROM PayrollTotals pts WHERE
pts.PTACID = 38
AND pts.PTAmount > 0
AND pts.PTEmpID = PayrollTotals.PTEmpID)
WHERE
PTACID = 42