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