SQL Update query

Question

Hi,

This SQL query does not execute. Can someone give the correct syntax?

UPDATE PayrollTotals ptm 
  SET PTAmount = PTAmount + 
    (SELECT pts.PTAmount FROM PayrollTotals pts WHERE pts.PTACID = 38 
       AND pts.PTAmount > 0 
       AND pts.PTEmpID= ptm.PTEmpID)
WHERE PTACID = 42

I want to update types 42 with types 38 where the PTEmpID match. Also sum PTAmount.

Edit: There will always be 38 and 42 row for each PTEmpID. So I just want to add 38 to 42 where PTEmpID match. The whole table that is.

Answer 1

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