Update Question - SQL Server Forums

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

Transact-SQL (2000)

Update Question

Author

Topic

BigRetina
Posting Yak Master

144 Posts

Posted - 2002-09-04 : 08:10:46

salute..
how can I update a table with the values returned from a select
e.g. :
Update Employees
SET LoanMoney = (SELECT MonthlyPayment FROM Loans)
or something like that??..
both tables contain EmployeeId field.
That is i want to fetch the MonthlyPayment for each employee and put it in the employee's LoanMoney field in Employees table..CAN I DO THAT IN ONE QUERY??
Thanks In Advance

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-09-04 : 08:12:42


update employees
    set loanmoney = l.monthlypayment
from
    employees e
    inner join loans l
        on e.employeeid = l.employeeid

Jay White
{0}

BigRetina
Posting Yak Master

144 Posts

Posted - 2002-09-04 : 08:27:15

quote:


update employees
    set loanmoney = l.monthlypayment
from
    employees e
    inner join loans l
        on e.employeeid = l.employeeid

how can I replace the l.monthlypayment with a summation of the monthlypayments of the employee??
Thanks For the FAST REPLY

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-09-04 : 08:50:29


update employees
    set loanmoney = l.payment
from
    employees e
    inner join (
        select
            employeeid,
            sum(monthlypayments) as payment
        from
            loans
        group by
            employeeid ) l
        on e.employeeid = l.employeeid

Jay White
{0}

Edited by - Page47 on 09/04/2002 09:00:34

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-04 : 09:00:02

or
update employees
set loanmoney = coalesce((select sum(monthlypayments) from loans where loans.employeeid = employees.employeeid),0)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

BigRetina
Posting Yak Master

144 Posts

Posted - 2002-09-04 : 09:37:17

THANK U

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources