SQL Update query

Question

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.

"Also sum PTAmount." can you clarify the summing part? perhaps with an example. — akf
– akf, Commented Jun 28, 2009 at 4:08

richardtallent · Accepted Answer · 2009-06-28 04:19:50Z

3

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

edited Jun 28, 2009 at 4:19

answered Jun 28, 2009 at 4:07

richardtallent

35.5k14 gold badges87 silver badges126 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Malcolm Over a year ago

It doesn't like the line 1 the "ptm" is that valid in a update clause??

richardtallent Over a year ago

I figured as much... fixed above.

Malcolm Over a year ago

Thankyou Richard it was the alias part I could not get right.

Collectives™ on Stack Overflow

SQL Update query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related