what's wrong with this update statement

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

SQL Server Development (2000)

what's wrong with this update statement

Author

Topic

Ali.M.Habib
Yak Posting Veteran

54 Posts

Posted - 2009-08-19 : 04:31:24

Always give error

I want to check the entered data before update

[code="sql"]

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

)

[/code]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-19 : 10:20:50

You can't imbed and IF block inside a sql DML statement. It is just for control of flow.

so change this:
update test set rec_hhmmsshs = (if exists (<select statement>) ) begin

to this:


if exists (<select statement>)
begin
    update test set...
end
else
begin
     update test set....
end

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources