Update and replace - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Update and replace

Author

Topic

rabisco
Starting Member

15 Posts

Posted - 2012-02-23 : 18:23:10

I have the following code

declare @myvar varchar(max)
declare @code1sku varchar(50)
declare @code2sku varchar(50)
declare @prereplace varchar(50)
declare @postreplace varchar(50)
declare @poscode1 int
set @myvar = ''
declare c1 cursor read_only
for
select bodytext from documentbody
open c1
fetch next from c1 into @myvar
while @@fetch_status = 0
begin
if charindex('code1=',@myvar) > 0 and charindex(code2=',@myvar) = 0
begin
set @poscode1 = (select patindex('%code1="%', @myvar))
set @code1sku = (select substring(@myvar, @poscode1 + 13,7))
set @code2sku = (select code2_sku from Linktable where code1_sku = @code1sku)
set @prereplace = 'code1="'+@code1sku+'"'
set @postreplace = @prereplace+' code2="'+@code2sku+'"'
set @myvar = replace(@myvar, @prereplace,@postreplace)
update jivedocumentbody
set bodytext = @myvar
where current of c1
end
fetch next from c1 into @myvar
end
close c1
deallocate c1

This is what I'm attempting to achieve....

for the sample data(This is the bodytext column of the documentbody table) - <body><aname="productLink" class="productLink" code1="1463513" >ProductLink</a></body>

For every paragraph , users should enter a value for code1 and code2.

The current situation.
Users have created some documents which have only values for code1 or code 2

The proposed solution.
1. A sql job to look through all the rows (bodytext column) of the documentbody table
2. Find an occurence of a value for code1,
3. Look for the corresponding code2 value in another table
4. Update the code2 value where it is missing.
So essentially, I'm trying to achieve this...<body><aname="productLink" class="productLink" code1="1463513" code2="4567891">ProductLink</a></body>

Note that some rows have multiple paragrahs . Essentially, the same operation will be carried out on every paragraph where there is one of code1 or code2 missing.

Any paragraphs or rows with both code1 or code2 missing, will not be processed.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-23 : 18:31:48

why dont you put this in xml field and do check using exist() function and do update using modify() method?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources