Updating a field based on another record

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Updating a field based on another record

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-28 : 08:43:21

Chris writes "I need to update a field based on a related record.

Ex.

TransID  LoanId  Type     Fee
100      1       New      10.00
101      1       Payoff   ????
102      2       New      25.00
103      3       New      20.00

What I need to do is to put the Fee value of TransId 100 into the Fee field of TransId 101.

This only needs to happen for situations where there are multiple transactions with the same LoanId.

How do I do it?

Thanks."

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-01-28 : 09:34:22

You can refer to the same table more than once in the same query, if you alias the table. The following should work:

UPDATE P SET P.Fee=N.Fee
FROM myTable P INNER JOIN myTable N ON (P.LoanID=N.LoanID)
WHERE P.Type='Payoff' AND N.Type='New'
AND P.Fee Is Null

This will update all the "Payoff" rows that have no fee; if you only want specific rows updated you'll need to modify the WHERE clause.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources