sheesh - how long should this take?

[k108]

Hi,

I am using a cursor to update about 2000 rows.

So far, it's been running 10 minutes!

This is the query:


DECLARE @custid INT

DECLARE cur1 CURSOR
FOR SELECT custid FROM customers_kl
WHERE custid IN (SELECT custid from _temp_customers_year)

OPEN cur1
FETCH NEXT FROM cur1 INTO @custid

WHILE @@FETCH_STATUS = 0
BEGIN

UPDATE customers_kl
SET renewaldate = (SELECT
CAST(
CAST( tc.year_renewaldate AS VARCHAR(4) ) + '-' +
CAST( MONTH(c.renewaldate) AS VARCHAR(2)) + '-' +
CAST( DAY(c.renewaldate) AS VARCHAR(2))
AS DATETIME)
FROM _temp_customers_year tc, customers_kl c
WHERE c.custid = @custid
AND c.custid = tc.custid)
WHERE CURRENT OF cur1

END

CLOSE cur1
DEALLOCATE cur1
GO

 

[hmckillop]

I think this is the same thing without the need for a cursor and should be considerably faster

 UPDATE tb1 
	SET renewaldate =   (SELECT 
                CAST(
                       CAST( tc.year_renewaldate AS VARCHAR(4) ) + '-' + 
                CAST( MONTH(c.renewaldate) AS VARCHAR(2)) + '-' + 
                CAST( DAY(c.renewaldate) AS VARCHAR(2))
                AS DATETIME)
                FROM _temp_customers_year tc, customers_kl c 
                WHERE c.custid = @custid
                AND c.custid = tc.custid)
    FROM customers_kl tb1
	INNER JOIN _temp_customers_year tb2 on tb1.custid = tb2.custid

"I'm living so far beyond my income that we may almost be said to be living apart

 

[k108]

Yes, I tried an inner join query first. The problem is, I am doing a positioned update on the rows. You can't do this because:

Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

This is an example of where a cursor is actually necessary. Fortunately, it is only a one time thing.

 

[k108]

What was missing was "FETCH NEXT..."

otherwise you get an infinite loop, that was the problem