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?
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.
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;