August 19, 2009 at 2:31 am
Always give error
I want to check the entered data before update
update test set REC_HHMMSSHS
=(
if exists(select cast(cast(abs(round(cast( left(cast(REC_HHMMSSHS 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(REC_HHMMSSHS 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(REC_HHMMSSHS 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(REC_HHMMSSHS as char(8)),7,2) as int)-1 *RAND(CAST(CAST(newid() as binary(8)) as INT)) ,0)) as char(2)) -- Parts of second
as binary(8)) from test
)
begin
cast(abs(round(cast( left(cast(REC_HHMMSSHS 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(REC_HHMMSSHS 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(REC_HHMMSSHS 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
end
else
begin
select cast(cast(abs(round(cast( left(cast(REC_HHMMSSHS 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(REC_HHMMSSHS 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(REC_HHMMSSHS 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(REC_HHMMSSHS as char(8)),7,2) as int)-1 *RAND(CAST(CAST(newid() as binary(8)) as INT)) ,0)) as char(2)) -- Parts of second
as binary(8))
end
)
August 19, 2009 at 3:42 am
You cant use IF / ELSE in DML queries.
You can use CASE / THEN / ELSE instead:
update test set REC_HHMMSSHS
= CASE
WHEN exists(select cast(cast(abs(round(cast( left(cast(REC_HHMMSSHS 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(REC_HHMMSSHS 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(REC_HHMMSSHS 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(REC_HHMMSSHS as char(8)),7,2) as int)-1 *RAND(CAST(CAST(newid() as binary(8)) as INT)) ,0)) as char(2)) -- Parts of second
as binary(8)) from test
)
THEN
cast(abs(round(cast( left(cast(REC_HHMMSSHS 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(REC_HHMMSSHS 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(REC_HHMMSSHS 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
ELSE
cast(cast(abs(round(cast( left(cast(REC_HHMMSSHS 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(REC_HHMMSSHS 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(REC_HHMMSSHS 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(REC_HHMMSSHS as char(8)),7,2) as int)-1 *RAND(CAST(CAST(newid() as binary(8)) as INT)) ,0)) as char(2)) -- Parts of second
as binary(8))
end
-- Gianluca Sartori
August 19, 2009 at 4:06 am
I'm not sure if the logic you're trying to implement will help to achieve the goal you're looking for:
As far as I can see your EXIST statement is pretty much the same as
SELECT REC_HHMMSSHS FROM test
This will always return true as soon as the table has at least one row. Is this your intention?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply