Problem with cursors for update 1

[9654]

Here i have written simple update cusror for experiment
and it is taking forever

DECLARE c1 CURSOR for
select scnt from support_date_exception
FOR UPDATE OF scnt

OPEN c1

FETCH NEXT FROM c1
WHILE @@FETCH_STATUS = 0

BEGIN

UPDATE support_date_exception set
scnt=null
WHERE CURRENT OF c1

END

CLOSE c1
DEALLOCATE c1

 

[gmmastros]

What are you trying to accomplish with this cursor? Are you trying to update the table setting the scnt field to null for every record?

If the above assumption is correct, then the following query should take care of it.

UPDATE support_date_exception set
scnt=null

without a where clause, the scnt field will be updated to null in EVERY record.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[9654]

I know this can be done using simple update but i was trying to write an update cursor and even though this table has just 40 records it is taking forever to update so i thought there is syntax issue.

Meanwhile the real issue is with this cursor
where while executing i get

Server: Msg 16957, Level 16, State 4, Line 2
FOR UPDATE cannot be specified on a READ ONLY cursor.

even though i have not mentioned it to be read only

DECLARE c1 CURSOR for
select distinct sxlo_loc.lo_udfchar1,
sxmr_maintren.mr_cycstartdt,
sxmr_maintren.mr_cycstopdt
from sxmr_maintren,sxlo_loc,
(select qry.transaction_id,qry.support_start,qry.support_end from
(SELECT distinct ltrim(rtrim(customer.cus_cd)) cust_id,
ltrim(rtrim(customer.cus_name)) cust_name,
ltrim(rtrim(sxlo_loc.lo_udfchar1)) transaction_id,
ltrim(rtrim(orddet.ordd_sku_cd)) sku,
sxmr_maintren.mr_cycstartdt support_start,
sxmr_maintren.mr_cycstopdt support_end,
ltrim(rtrim(orddet.ordd_desc)) description,
case upper(ltrim(rtrim(sxmr_maintren.mr_plan)))
when 'SILVER' then 'Silver (S0100A)'
when 'GOLD' then 'Gold (S0101A)'
when 'PLATINUM' then 'Platinum (S0102A)'
else ltrim(rtrim(sxmr_maintren.mr_plan))
end support_offering,
ltrim(rtrim(shipto.s2_city)) city,
ltrim(rtrim(shipto.s2_state)) state,
ltrim(rtrim(shipto.s2_country)) country,
ltrim(rtrim(shipto.s2_xemail)) ship_email,
ltrim(rtrim(customer.cus_xemail1)) e1,
ltrim(rtrim(orddet.ordd_ord_no)) ordno
FROM customer customer,
sxcu_cust sxcu_cust ,
shipto shipto,
ord ord,
sxpr_prod sxpr_prod,
sxmr_maintren sxmr_maintren,
orddet orddet,
sxos_ordd_sales sxos_ordd_sales,
sxlo_loc sxlo_loc,
salesper salesper
where customer.cus_cd = shipto.s2_cus_cd AND
customer.cus_grp_cd = shipto.s2_cus_grp AND
customer.cus_ship_to = shipto.s2_location AND
customer.cus_cd = ord.ord_cus_cd AND
customer.cus_grp_cd = ord.ord_cus_grp AND
sxcu_cust.cus_cd = ord.ord_cus_cd AND
sxcu_cust.sxcu_pk = sxpr_prod.sxcu_pk AND
sxpr_prod.sxpr_pk = sxmr_maintren.sxpr_pk AND
ord.ord_company = orddet.ordd_ord_co AND
ord.ord_no = orddet.ordd_ord_no AND
sxmr_maintren.ord_no = orddet.ordd_ord_no AND
orddet.ordd_ord_co = sxos_ordd_sales.sxos_ordd_ord_co AND
orddet.ordd_ord_no = sxos_ordd_sales.sxos_ordd_ord_no AND
orddet.ordd_seq_no = sxos_ordd_sales.sxos_ordd_seq_no AND
sxmr_maintren.sxlo_pk = sxlo_loc.sxlo_pk AND
sxos_ordd_sales.sxos_sal_cd = salesper.sal_cd
)qry
group by qry.transaction_id,qry.support_start,qry.support_end)qry2
where
sxmr_maintren.sxlo_pk = sxlo_loc.sxlo_pk and
ltrim(rtrim(sxlo_loc.lo_udfchar1))=qry2.transaction_id and
sxmr_maintren.mr_cycstartdt=qry2.support_start and
sxmr_maintren.mr_cycstopdt=qry2.support_end
and ltrim(rtrim(sxlo_loc.lo_udfchar1)) in
(select qry.transaction_id from
(SELECT distinct ltrim(rtrim(customer.cus_cd)) cust_id,
ltrim(rtrim(customer.cus_name)) cust_name,
ltrim(rtrim(sxlo_loc.lo_udfchar1)) transaction_id,
ltrim(rtrim(orddet.ordd_sku_cd)) sku,
sxmr_maintren.mr_cycstartdt support_start,
sxmr_maintren.mr_cycstopdt support_end,
ltrim(rtrim(orddet.ordd_desc)) description,
case upper(ltrim(rtrim(sxmr_maintren.mr_plan)))
when 'SILVER' then 'Silver (S0100A)'
when 'GOLD' then 'Gold (S0101A)'
when 'PLATINUM' then 'Platinum (S0102A)'
else ltrim(rtrim(sxmr_maintren.mr_plan))
end support_offering,
ltrim(rtrim(shipto.s2_city)) city,
ltrim(rtrim(shipto.s2_state)) state,
ltrim(rtrim(shipto.s2_country)) country,
ltrim(rtrim(shipto.s2_xemail)) ship_email,
ltrim(rtrim(customer.cus_xemail1)) e1,
ltrim(rtrim(orddet.ordd_ord_no)) ordno
FROM customer customer,
sxcu_cust sxcu_cust ,
shipto shipto,
ord ord,
sxpr_prod sxpr_prod,
sxmr_maintren sxmr_maintren,
orddet orddet,
sxos_ordd_sales sxos_ordd_sales,
sxlo_loc sxlo_loc,
salesper salesper
where customer.cus_cd = shipto.s2_cus_cd AND
customer.cus_grp_cd = shipto.s2_cus_grp AND
customer.cus_ship_to = shipto.s2_location AND
customer.cus_cd = ord.ord_cus_cd AND
customer.cus_grp_cd = ord.ord_cus_grp AND
sxcu_cust.cus_cd = ord.ord_cus_cd AND
sxcu_cust.sxcu_pk = sxpr_prod.sxcu_pk AND
sxpr_prod.sxpr_pk = sxmr_maintren.sxpr_pk AND
ord.ord_company = orddet.ordd_ord_co AND
ord.ord_no = orddet.ordd_ord_no AND
sxmr_maintren.ord_no = orddet.ordd_ord_no AND
orddet.ordd_ord_co = sxos_ordd_sales.sxos_ordd_ord_co AND
orddet.ordd_ord_no = sxos_ordd_sales.sxos_ordd_ord_no AND
orddet.ordd_seq_no = sxos_ordd_sales.sxos_ordd_seq_no AND
sxmr_maintren.sxlo_pk = sxlo_loc.sxlo_pk AND
sxos_ordd_sales.sxos_sal_cd = salesper.sal_cd
)qry
group by qry.transaction_id
having count(distinct qry.support_start)>1 or count(distinct qry.support_end)>1)
FOR UPDATE OF lo_udfchar1


OPEN c1

FETCH NEXT FROM c1
WHILE @@FETCH_STATUS = 0

BEGIN

UPDATE sxlo_loc set
lo_udfchar1 = replace(lo_udfchar1,substring(lo_udfchar1,CHARINDEX( '0000',lo_udfchar1) ,CHARINDEX( '##',lo_udfchar1)- CHARINDEX( '0000',lo_udfchar1)),
'_'+cast(datepart(yyyy,mr_cycstartdt)as varchar(4))+
(case len(cast(datepart(dd,mr_cycstartdt)as varchar(2))) when 1 then '0'+cast(datepart(dd,mr_cycstartdt)as varchar(2))else cast(datepart(dd,mr_cycstartdt)as varchar(2)) end) +
(case len(cast(datepart(mm,mr_cycstartdt)as varchar(2))) when 1 then '0'+cast(datepart(mm,mr_cycstartdt)as varchar(2))else cast(datepart(mm,mr_cycstartdt)as varchar(2)) end) +
+'_'+cast(datepart(yyyy,mr_cycstopdt)as varchar(4))+
(case len(cast(datepart(dd,mr_cycstopdt)as varchar(2))) when 1 then '0'+cast(datepart(dd,mr_cycstopdt)as varchar(2))else cast(datepart(dd,mr_cycstopdt)as varchar(2)) end) +
(case len(cast(datepart(mm,mr_cycstopdt)as varchar(2))) when 1 then '0'+cast(datepart(mm,mr_cycstopdt)as varchar(2))else cast(datepart(mm,mr_cycstopdt)as varchar(2)) end)+'_' )
WHERE CURRENT OF c1


END

CLOSE c1
DEALLOCATE c1

 

[gmmastros]

On this page:

http://msdn2.microsoft.com/en-us/library/ms180169.aspx

There is a statement about...

If the SELECT statement does not support updates (insufficient permissions, accessing remote tables that do not support updates, and so on), the cursor is READ_ONLY.

And on this page...

http://support.microsoft.com/default.aspx?scid=kb;en-us;158773

If a cursor is declared in SQL Server 6.0 with a select statement that does any of the following, the cursor will actually be created as a static, read-only cursor:
• Does not reference any tables with at least one unique index or primary key constraint.
• Contains DISTINCT, UNION, GROUP BY, or HAVING clauses.
• Contains a constant expression in the select list.

You may want to try...
1. Select the data in to a table variable or temp table with an identity column.
2. Set up a while loop
3. Each interation through the while loop, you get the date and update the table that needs to be updated.

Ex...

Delcare @Temp 
Table   (RowId Integer Identity(1,1), 
        Field1 Int,
        Field2 VarChar(20),
        etc...)

Insert Into @Temp(Field1, Field2)
Select .... (your big query here)

Declare @iMax Integer
Declare @i Integer

Select @i = 1,
       @iMax = Max(RowId)
From   @Temp

Declare @Field1 Integer
Delcare @Field2 VarChar(20)

While @i <= @iMax
  Begin
    Select @Field1 = Field1,
           @Field2 = Field2
    From   @Temp
    Where  RowId = @i

    Update Table... (put an update query here)

    Set @i = @i + 1
  End

Performance ??? I don't know. If you can re-write this in a set based way, performance would be better. If you can't re-write it, then the previous method is probably your best bet.

In any case, I wish you luck.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom