Cursor running more than once? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Cursor running more than once?

Author

Topic

dadunn
Yak Posting Veteran

67 Posts

Posted - 2001-10-18 : 18:29:16

I have a table that I am updating with data in a temporary table.

I am using a cursor to step through the table I want to update so that I can perform the needed test.

I don't want it to be a cursor, but that's beside the point.

In the cursor should be 503 rows. We were getting some strange results and put in a counter between the fetches.

It looks like the code is running through the cursor several times and I don't know how this would be possible.

Here is the code and I appreciate any help.

Dave

/***********************INSERT LOTTABLES INTO IMPORT940 TABLE**************************/
Declare @d_susr5 char(30), @d_sku char(20)

Declare CURSOR_IMPORT940LOTTABLES CURSOR for
select d_susr5,d_sku from import940 order by d_orderkey,d_sku
Open CURSOR_IMPORT940LOTTABLES
Fetch NEXT from CURSOR_IMPORT940LOTTABLES into @d_susr5,@d_sku
While @@FETCH_STATUS = 0
Begin
update import940 set d_lottable01 = (Select top 1 lottable01 from #INSERTLOTTABLE where sku = @d_sku order by editdate,lottable01)
where d_sku = @d_sku and d_susr5 = @d_susr5

delete from #INSERTLOTTABLE where lottable01 = (Select top 1 lottable01 from #INSERTLOTTABLE where sku = @d_sku
order by editdate,lottable01)

Fetch NEXT from CURSOR_IMPORT940LOTTABLES into @d_susr5,@d_sku
End

close CURSOR_IMPORT940LOTTABLES

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-17 : 15:24:07

its friday afternoon . . .what else to do but hunt and kill cursors . . .


update import940
set d_lottable01 = l.minlottable01
from
    import940 i
    inner join (
        select
            sku,
            min(lottable01) as minlottable01
        from
           (select
                sku,
                min(editdate) mineditdate
            from
                #insertlottable i
            group by
                sku ) minedit
            inner join #insertlottable ilt
                on (ilt.sku = minedit.sku and
                    ilt.editdate = minedit.mineditdate) minlot
        group by
            sku ) l
        on i.d_sku = l.sku

delete #insertlottable
from #insertlottable l
where exists (
    select 1
    from
        import940
    where
        d_sku = l.sku and
        d_lottable01 = l.lottable01 )

<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources