SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tooba
Posting Yak Master

143 Posts

Posted - 02/07/2013 :  19:59:18  Show Profile  Reply with Quote
Here is my sample data table

Table Name = Test
ID,DTypeID,MinDuration,MaxDuration,UpdateField
1,123,0,12
2,345,6,18
3,111,12,24

Here is my SP (Inside SP)
-- I am update above table

Update Test
Set UpdateField = Right
from Test
where MinDuration < 12


Update Test
Set UpdateField = Left
From Test
Where MinDuration >= 12


My 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

143 Posts

Posted - 02/07/2013 :  21:18:48  Show Profile  Reply with Quote
I change the Query Little bit...

Table Name = Test (Here is my main Table)
ID,DTypeID,MinDuration,MaxDuration,UpdateField
1,123,0,12
2,345,6,18
3,111,12,24

Here is my SP (Inside SP)
-- I am update above table
Select * into #Temp1 from Test

Update #Temp1
Set UpdateField = Low
from Test
where MinDuration = 12


Update #Temp1
Set UpdateField = High
From Test
Where MinDuration >= 12

The End Result Its Depend (Let say its meet first Update requirement) then End result should be
Select * from #Temp1
ID,DTypeID,MinDuration,MaxDuration,UpdateField
1,123,0,12, Low
2,345,6,18
3,111,12,24

If first Update statement doesn't meed the requirement then End result should be

Select * from #Temp1
ID,DTypeID,MinDuration,MaxDuration,UpdateField
1,123,0,12
2,345,6,18, High
3,111,12,24, High

Thank You....
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/07/2013 :  23:09:35  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

tooba
Posting Yak Master

143 Posts

Posted - 02/08/2013 :  01:00:55  Show Profile  Reply with Quote
Visakh Thanks for Response. Here is correct Syntax

Update #Temp1
Set UpdateField = Low
from Test
where MinDuration = 12


Update #Temp1
Set UpdateField = High
From Test
Where MinDuration > 12

The End Result Its Depend (Let say its meet first Update requirement) then End result should be
Select * from #Temp1
ID,DTypeID,MinDuration,MaxDuration,UpdateField
1,123,0,12, Low
2,345,6,18
3,111,12,24

If first Update statement doesn't meed the requirement then End result should be

Select * from #Temp1
ID,DTypeID,MinDuration,MaxDuration,UpdateField
1,123,0,12
2,345,6,18, High
3,111,12,24, High


Visakh for Above sample data i can use

Update #Temp1
Set UpdateField = case when MinDuration < 12 then Low else High end
from #Temp1

But i am looking something else. How about
if i use

IF UpdateField is null or IF UpdateField = 0
begin
First Update Statement
end
Else
IF UpdateField = = 0
begin
My Second Update Statement
end

Note:- In actual data i have more then two fields to update. Does this sounds right to you?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/08/2013 :  03:37:02  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

tooba
Posting Yak Master

143 Posts

Posted - 02/08/2013 :  11:50:55  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/08/2013 :  23:06:06  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

tooba
Posting Yak Master

143 Posts

Posted - 02/10/2013 :  00:30:31  Show Profile  Reply with Quote
Yes i have others column to, In real it could be more then 12 Update statements. Any advice?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/11/2013 :  00:25:23  Show Profile  Reply with Quote
in that you should use a set of if else constructs to do update based on your criteria

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.23 seconds. Powered By: Snitz Forums 2000