putting statment in update - 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 2000 Forums

SQL Server Development (2000)

putting statment in update

Author

Topic

Ex
Posting Yak Master

166 Posts

Posted - 2005-01-06 : 17:33:25

hey all
just wondering if anyone can help me

i am trying to get rid of a cursor inside the cursor i have this

select @UnknownSizeCount = count(*) from entity_item
where entityId = @EntityID AND SizeUpdated = 0 AND MajorBuildNo = 0 AND MinorBuildNo = 0

if (@UnknownSizeCount = 0)
BEGIN

UPDATE dbo.ENTITY SET NewSizeOfEntity = sum ((e.NewSizeOfEntity + case when e.AutoSize = 1 then 0 else e.SparesInBytes end) * ei.TotalDimElements), SizeUpdated = 1
from dbo.Entity e, dbo.Entity_Item ei
where
e.EntityId = ei.TypeId AND
e.MajorBuildNo = 0 AND e.MinorBuildNo = 0 AND
ei.MinorBuildNo = 0 AND ei.MajorBuildNo = 0 AND
ei.SizeUpdated = 0 AND

END

can someone help me work out how to put the if condition within the update statment plz, sorry i just cant seem to work it out

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-06 : 18:07:04

if literally then

UPDATE dbo.ENTITY SET

NewSizeOfEntity =
CASE WHEN (select count(*) from entity_item where entityId = @EntityID AND
SizeUpdated = 0 AND MajorBuildNo = 0 AND MinorBuildNo) = 0
THEN
sum((e.NewSizeOfEntity + case when e.AutoSize = 1 then 0 else e.SparesInBytes end) *
ei.TotalDimElements)
ELSE NewSizeOfEntity END,

SizeUpdated =
CASE WHEN (select count(*) from entity_item where entityId = @EntityID AND
SizeUpdated = 0 AND MajorBuildNo = 0 AND MinorBuildNo) = 0
THEN 1
ELSE SizeUpdated END

from dbo.Entity e, dbo.Entity_Item ei
where
e.EntityId = ei.TypeId AND
e.MajorBuildNo = 0 AND e.MinorBuildNo = 0 AND
ei.MinorBuildNo = 0 AND ei.MajorBuildNo = 0 AND
ei.SizeUpdated = 0 AND

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2005-01-06 : 18:09:55

Will this work for you?


UPDATE e
SET NewSizeOfEntity = sum ((e.NewSizeOfEntity + case when e.AutoSize = 1 then 0 else e.SparesInBytes end) * ei.TotalDimElements), SizeUpdated = 1
from dbo.Entity e, dbo.Entity_Item ei
where
e.EntityId = ei.TypeId AND
e.MajorBuildNo = 0 AND e.MinorBuildNo = 0 AND
ei.MinorBuildNo = 0 AND ei.MajorBuildNo = 0 AND
ei.SizeUpdated = 0 AND 
NOT EXISTS(SELECT * FROM Entity_Item WHERE EntityID = e.EntityID AND SizeUpdated = 0 AND MajorBuiildNo = 0 AND MinorBuildNo = 0)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources