CODEWITHSUNDEEP
sql
jhammond
jhammond

Reputation: 2086

Updating a field based on multiple criteria

For reference, this isn't a homework assignment, but something I am doing at work. Unfortunately, my SQL skills are not very good, so I'm not sure how to go about doing this. I know there are some great SQL guys here, so I was hoping you could help me out!

At the very least, any guidance on the general structure of the query would be awesome! The primary issue I have is that the heart of this query will have to be run multiple times (ie. A loan has multiple rows that have Payoff = 1)

Thanks!

Suppose you have the following tables:

Payoffs:
P_ID | DebtID (FK) | LoanID | Payoff | PayoffAmount
=====|=============|========|========|=============
1    | 1           | 10     | 1      | 0.00
2    | 2           | 10     | 1      | 0.00
3    | 3           | 27     | 0      | 0.00
4    | 4           | 14     | 1      | 0.00
5    | 5           | 10     | 0      | 0.00

Debts:
DebtID | CurrAccountBal
=======|===============
1      | 2375.00
2      | 1000.00
3      | 2300.00
4      | 2400.00
5      | 500.00

Given a LoanID, if Payoffs.Payoff = "1", then take the amount in Debts.CurrAccountBalance and insert it into Payoffs.PayoffAmount.

Upvotes: 0

Views: 49

Answers (1)

user700390
user700390

Reputation: 2339

This should work in most (if not all) database engines:

UPDATE Payoffs
SET    PayoffAmount =
       (SELECT CurrAccountBal
        FROM Debts
        WHERE Payoffs.DebtID = Debts.DebtID
       )
WHERE  Payoff = 1
  AND  LoanID = [insert desired LoanID here];

Upvotes: 1

Related Questions