updating

[camy123]

hi can any one help me please ive written this script to update dynamically but none of the updates work .. does any one know here is the code

thanks in advance

declare @maxCount int
declare @minCount int
declare @innerloopmincount int
declare @innerloopmaxcount int
declare @tablename varchar(200)
declare @comparisonValue varchar(200)
declare @currentColumn varchar(200)
declare @mastercolmap varchar(200)
declare @duplicatecolmap varchar(200)
--declare @masterV varchar(200)
--declare @DuplicateV varchar(200)


select @maxCount = count(*) from #tablenames
set @minCount = 0

while @mincount < @maxcount
begin
print 'outer loop'
select top 1 @tablename = tablename from #tablenames where checked = 0
print @tablename
select @innerloopmaxcount = count(*) from #columnnames
set @innerloopmincount = 0

while @innerloopmincount < @innerloopmaxcount
begin
print 'inerloop'
select @comparisonValue = comparisoncol from #TableNames where tablename = + @tablename
select top 1 @currentColumn = colname from #columnnames where checked = 0
print @currentColumn
select @mastercolmap = mastercolmap from #TableNames where tablename =@tablename
select @duplicatecolmap = duplicatecolmap from #TableNames where tablename =@tablename

declare @sql nvarchar(4000)

set @sql = 'declare @masterV varchar(2000)'
+ ' Declare @Duplicatev varchar(2000)'
+ ' declare @minloopVal int'
+ ' declare @maxloopval int'
+ ' declare @Mval nvarchar(200)'
+ ' declare @dval nvarchar(200)'
+ ' declare @goto_v varchar(200)'
+ ' set @minloopval = 0'
+ ' select @maxloopval = count(*) from dupes'
+ ' update dupes set checked = 0'
+ ' while @minloopval < @maxloopval'
+ ' begin'
+ ' print @minloopval'
+ ' select @goto_v = colgoto from #columnnames where colname = ' + '''' + @currentColumn + ''''
+ ' select top 1 @masterV =' + @mastercolmap + ' from dupes where checked != 1 '
+ ' select top 1 @Duplicatev=' + @duplicatecolmap + ' from dupes where checked != 1'
+ ' select @masterV = convert(uniqueidentifier,@masterV)'
+ ' select @Duplicatev = convert(uniqueidentifier,@Duplicatev)'
+ ' print @masterV'
+ ' if @goto_v = 10 '
+ ' begin'
+ ' select @Mval =' + @currentColumn + ' from ' + @tablename + ' where '+ @comparisonValue + '=@masterv'
+ ' select @dval =' + @currentColumn + ' from ' + @tablename + ' where '+ @comparisonValue + '=@Duplicatev'
+ ' select @mval = isnull(@mval,''0'')'
+ ' select @dval = isnull(@dval,''0'')'
+ ' if @mval >= @dval or @mval = 1'
+ ' begin'
+ ' print ''no action taken'''
+ ' end'
+ ' else'
+ ' begin'
+ ' update ' + @tablename + ' set ' + @currentColumn + '= @dval where ' + @comparisonValue + ' = @masterv '
+ ' end '
+ ' end '
+ ' update dupes set checked = 1 where ' + @mastercolmap + '= @masterv'
+ ' delete from ' + @tablename + ' where ' + @comparisonValue + '= @Duplicatev'
+ ' set @minloopval = @minloopval + 1'
+ ' end'


exec sp_executesql @sql

update #columnnames set checked ='1' where colname = @currentColumn
set @innerloopmincount = @innerloopmincount + 1
end
update #tablenames set checked ='1' where tablename = @tablename
set @mincount = @mincount + 1
end

 

[rsshetty]

are you getting an error msg?

rsshetty.
It's always in the details.

 

[camy123]

nah the same row keeps loooping
cuase
this statement
update dupes set checked = 1 where ' + @mastercolmap + '= @masterv'

should update a checked column to 1 so it moves on to the next row but becuase it is not updating the same row keeps coming up . :0(

 

[vongrunt]

print that from dynamic SQL and post generated SQL here.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[camy123]

0
BFB0454B-BB66-4AA6-B183-0D9F8B9743F6
no action taken

(0 row(s) affected)


(0 row(s) affected)

1
BFB0454B-BB66-4AA6-B183-0D9F8B9743F6
no action taken

(0 row(s) affected)


(0 row(s) affected)

2
BFB0454B-BB66-4AA6-B183-0D9F8B9743F6
no action taken

(0 row(s) affected)


(0 row(s) affected)

3
BFB0454B-BB66-4AA6-B183-0D9F8B9743F6



as you can see the same value keeps looping..

 

[vongrunt]

Not that... print entire generated UPDATE statement... something like this (insert line in bold):

...
+ ' end '
[b]+ ' print(''update dupes set checked = 1 where '' + @mastercolmap + ''= @masterv'') '[/b]
+ ' update dupes set checked = 1 where ' + @mastercolmap + '= @masterv' 
...

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba