SQL Update table based on conditions in separate table

I have two tables each containing thousands of records on security deposits that have been paid or are owed. The format is like this:

Table A
ID   ID2    ID3   Payment
1    REG    P     102.5
2    REG          120
3    REG    P     65.5

Table B
ID     Payment
1      17.5
2      0
3      45.5

Where Table A shows the amount that has already been paid and table B shows the amount that is still due. I would like to update table B so that any time a security deposit has not been fully paid off, I show the total amount charged. For example, for ID 1, I would like to update B.Payment to be equal to A.Payment+B.Payment but I don't want ID 2 to be updated because this deposit has already been paid in full. I tried using the following code but it updates every row in my table instead of just the rows with partial payments:

UPDATE B 
SET B.[Payment] = (A.[Payment] + B.[Payment]) 
FROM B LEFT JOIN A ON A.[ID] = B.[ID] 
WHERE A.[ID2] = 'REG' and A.[ID3] = 'P' 

How do I only update fields in table B when conditions in table A are met?

Answers


Try this:

UPDATE b SET b.Payment = b.Payment + a.Payment
FROM B b 
    JOIN (SELECT a.ID, Payment = sum(Payment) FROM A a 
          WHERE a.ID2 = 'REG' AND a.ID3 = 'P'
          GROUP BY a.ID) a ON b.ID = a.ID

It sums all payments in table A for each ID, then adds it to the Payment column in table B.


Am not sure on what b.id2 refers to in your code.

Try this

UPDATE b
SET b.payment = b.payment +
                (SELECT a.payment FROM a WHERE a.id=b.id AND a.id2='REG')
WHERE b.payment<>0;

Need Your Help

Send request with auth token in header in angularjs

angularjs spring jwt

I implemented jwt stateless in backend , so except login and signup all other method has to intercept in angularjs and sending auth token to server in request header .But token is not sending mean ...

asp.net mvc routing to a custom handler instead of controller?

asp.net-mvc-2 url-rewriting url-routing httphandler

I have a custom handler I've written that executes a custom action based on a parameter, for example: