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.
Author |
Topic |
tooba
Posting Yak Master
224 Posts |
Posted - 2013-02-07 : 19:59:18
|
Here is my sample data tableTable Name = TestID,DTypeID,MinDuration,MaxDuration,UpdateField1,123,0,122,345,6,183,111,12,24Here is my SP (Inside SP)-- I am update above table Update TestSet UpdateField = Rightfrom Testwhere MinDuration < 12Update TestSet UpdateField = LeftFrom TestWhere MinDuration >= 12My Question is :- I want when i run above store procedure if First Update Complete don't go to second update (For E.g if First Creteria meet the requirement and update "UpdateField to Right" don't go to Next Update Statement.Any help would be great appreciate.Thank You. |
|
tooba
Posting Yak Master
224 Posts |
Posted - 2013-02-07 : 21:18:48
|
I change the Query Little bit...Table Name = Test (Here is my main Table)ID,DTypeID,MinDuration,MaxDuration,UpdateField1,123,0,122,345,6,183,111,12,24Here is my SP (Inside SP)-- I am update above tableSelect * into #Temp1 from TestUpdate #Temp1 Set UpdateField = Lowfrom Testwhere MinDuration = 12Update #Temp1Set UpdateField = HighFrom TestWhere MinDuration >= 12The End Result Its Depend (Let say its meet first Update requirement) then End result should beSelect * from #Temp1ID,DTypeID,MinDuration,MaxDuration,UpdateField1,123,0,12, Low2,345,6,183,111,12,24If first Update statement doesn't meed the requirement then End result should beSelect * from #Temp1ID,DTypeID,MinDuration,MaxDuration,UpdateField1,123,0,12 2,345,6,18, High3,111,12,24, HighThank You.... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-07 : 23:09:35
|
you sample output and logic doesnt match. your logic looks for value of MinDuration but output shows as if you're considering Maxdurtaion ( see second case, you've no records with MinDuration > 12 and still it shows two rows with High. those rows have MaxDuration > 12 but even in that case i would expect first row also to be High as it has value as 12 and your condition reads >=12). Can you please clearly restate your requirements?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
tooba
Posting Yak Master
224 Posts |
Posted - 2013-02-08 : 01:00:55
|
Visakh Thanks for Response. Here is correct SyntaxUpdate #Temp1Set UpdateField = Lowfrom Testwhere MinDuration = 12Update #Temp1Set UpdateField = HighFrom TestWhere MinDuration > 12The End Result Its Depend (Let say its meet first Update requirement) then End result should beSelect * from #Temp1ID,DTypeID,MinDuration,MaxDuration,UpdateField1,123,0,12, Low2,345,6,183,111,12,24If first Update statement doesn't meed the requirement then End result should beSelect * from #Temp1ID,DTypeID,MinDuration,MaxDuration,UpdateField1,123,0,122,345,6,18, High3,111,12,24, HighVisakh for Above sample data i can useUpdate #Temp1Set UpdateField = case when MinDuration < 12 then Low else High end from #Temp1But i am looking something else. How aboutif i useIF UpdateField is null or IF UpdateField = 0 begin First Update StatementendElseIF UpdateField = = 0 begin My Second Update StatementendNote:- In actual data i have more then two fields to update. Does this sounds right to you? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-08 : 03:37:02
|
why do you need to write separate update statements?so far as filters are same you can accomodate them in same update itself------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
tooba
Posting Yak Master
224 Posts |
Posted - 2013-02-08 : 11:50:55
|
The Reason why i need it because i want to use atleast 12 Update Statements. If one of the criteria match update that column and get out from the loop. Any advise? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-08 : 23:06:06
|
quote: Originally posted by tooba The Reason why i need it because i want to use atleast 12 Update Statements. If one of the criteria match update that column and get out from the loop. Any advise?
as far as i can see the only criteria is based on MinDuration column.Do you mean you've other column also to determine criteria.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
tooba
Posting Yak Master
224 Posts |
Posted - 2013-02-10 : 00:30:31
|
Yes i have others column to, In real it could be more then 12 Update statements. Any advice? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-11 : 00:25:23
|
in that you should use a set of if else constructs to do update based on your criteria------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|