August 19, 2009 at 2:00 am
when I run the following code get the following error:
(1 row(s) affected)
(0 row(s) affected)
Server: Msg 16947, Level 10, State 1, Line 68
No rows were updated or deleted.
The statement has been terminated.
(1 row(s) affected)
(116989 row(s) affected)
-------- Designed at 18 August 2009
declare @newTime char(8)
set @newTime=''
declare @previous char(8)
set @previous =''
declare @Temp1 char(8)
set @Temp1 =''
declare @Temp2 char(8)
set @Temp2 =''
declare @flag int
set @flag=0
declare @Timetemp table
(
DTIME char(8)
)
declare Dat CURSOR FOR
select REC_HHMMSSHS from Test
OPEN Dat
FETCH NEXT FROM Dat INTO @previous
WHILE @@FETCH_STATUS = 0
BEGIN
set @Temp1 =
cast(abs(round(cast( left(cast(@previous as char(8)) , 2) as int)-1 *RAND(CAST(CAST(newid() as binary(8)) as INT)),0 )) as char(2)) -- Hours
+
cast(abs(round(cast( substring( cast(@previous as char(8)),3,2) as int)-1 *RAND(CAST(CAST(newid() as binary(8)) as INT)),0 )) as char(2)) -- Minutes
+
cast(abs(round(cast( substring( cast(@previous as char(8)),5,2) as int)-1 *RAND(CAST(CAST(newid() as binary(8)) as INT)) ,0)) as char(2)) -- seconds
+
cast(abs(round(cast( substring( cast(@previous as char(8)),7,2) as int)-1 *RAND(CAST(CAST(newid() as binary(8)) as INT)) ,0)) as char(2)) -- Parts of second
set @Temp2=
cast(abs(round(cast( left(cast(@previous as char(8)) , 2) as int)-1 *RAND(CAST(CAST(newid() as binary(8)) as INT)),0 )) as char(2)) -- Hours
+
cast(abs(round(cast( substring( cast(@previous as char(8)),3,2) as int)-1 *RAND(CAST(CAST(newid() as binary(8)) as INT)),0 )) as char(2)) -- Minutes
+
cast(abs(round(cast( substring( cast(@previous as char(8)),5,2) as int)-1 *RAND(CAST(CAST(newid() as binary(8)) as INT)),0)) as char(2)) -- seconds
+
cast(abs(round(60 * RAND(CAST(CAST(newid() as binary(8)) as INT)),0)) as char(2)) -- Parts of second
if(@Temp1 NOT IN (select * from @Timetemp ))
BEGIN
set @newTime= @Temp1
set @flag=2
END
ELSE IF(@Temp2 NOT IN (select * from @Timetemp ))
BEGIN
set @newTime= @Temp2
set @flag=1
END
--print(@newTime)
insert into @Timetemp values(@newTime)
--print 'DONE'
update Test set REC_HHMMSSHS = convert(binary(8),'00000000' ) where REC_HHMMSSHS = cast(@previous as binary(8))
--print 'DONE'
update Test set REC_HHMMSSHS = convert(binary(8),@newTime) WHERE CURRENT OF Dat
--print 'DONE'
if(@flag =1)
update Test set REC_HHMMSSHS = cast(@Temp1 as binary(8) ) where REC_HHMMSSHS = cast( '00000000' as binary(8))
if(@flag =2)
update Test set REC_HHMMSSHS = cast(@Temp2 as binary(8)) where REC_HHMMSSHS = cast( '00000000' as binary(8))
break
set @flag=0
FETCH NEXT FROM Dat INTO @previous
END
CLOSE Dat
DEALLOCATE Dat
select distinct REC_HHMMSSHS from Test
--select * from Test where cast( REC_HHMMSSHS as char(8)) ='9 4 5759'
August 19, 2009 at 4:09 am
the error simply is complaining that the positioned update failed to update a row
However I'm sure this cursor can be better written - can you post table definition for table 'test', and some sample data
August 19, 2009 at 7:56 am
kevriley (8/19/2009)
the error simply is complaining that the positioned update failed to update a rowHowever I'm sure this cursor can be better written - can you post table definition for table 'test', and some sample data
create table Test
(
INST_NO binary(3),
PROS_YYYYMMDD binary(8),
REC_YYYYMMDD binary(8),
REC_HHMMSSHS binary(8),
FROM_INST_NO binary(3),
FROM_ACCT_NO binary(16)
)
the first four columns be used as primary key
August 19, 2009 at 8:13 am
kevriley (8/19/2009)
the error simply is complaining that the positioned update failed to update a rowHowever I'm sure this cursor can be better written - can you post table definition for table 'test', and some sample data
I want to randamize the column REC_YYYYMMDD so no equal data be found at this column
do u have any idea
and at the same time not give error cause of primary key
August 19, 2009 at 8:33 am
It would be much better if you could provide some sample data and expected results.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply