Update row with cursor joining to another table

microsoft.public.sqlserver.programming

8 Sep 2006 3:53 PM

wnfisba

I have the following cursor process. My syntax joining to another table is
failing. Is this possible??? Joining to another table to get a data value for
the update within a cursor???

WHILE    @@FETCH_STATUS=0
BEGIN
    UPDATE    DMD_Data.dbo.custom_data
        SET    file_id    =    GEN.file_id,
            field    =    @act_01
    FROM    DMD_Data.dbo.GEN    GEN
    WHERE    CURRENT OF Inv_Cash_Prem_Cursor
        INNER JOIN    @Loan_Num    =    GEN.loan_num
    FETCH NEXT FROM Inv_Cash_Prem_Cursor    INTO    @Loan_Num, @act_01
END

8 Sep 2006 4:41 PM

David Browne

<DIV>"wnfisba" <wnfi***@discussions.microsoft.com> wrote in

message news:588BC22E-D9D6-4447-AF2C-C525F0E23CA3@microsoft.com...</DIV>>I

have the following cursor process. My syntax joining to another table is
Show quote

> failing. Is this possible??? Joining to another table to get a data value
> for
> the update within a cursor???
>
> WHILE @@FETCH_STATUS=0
> BEGIN
> UPDATE DMD_Data.dbo.custom_data
> SET file_id = GEN.file_id,
> field = @act_01
> FROM DMD_Data.dbo.GEN GEN
> WHERE CURRENT OF Inv_Cash_Prem_Cursor
> INNER JOIN @Loan_Num = GEN.loan_num
> FETCH NEXT FROM Inv_Cash_Prem_Cursor INTO @Loan_Num, @act_01
> END

You probably shouldn't be using a cursor for this. Can't you just issue a
single UPDATE statement joining the target table with the other tables
involved?

David