469,282 Members | 1,822 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,282 developers. It's quick & easy.

if else statement inside a where clause

Good day!!

I was wondering if it is possible to put an if else statement inside a where clause of an sql statement... you see i would like my update statement to do this:


update trial_clear set num = @count2 /* @count2 is a integer passed*/
where
if (select top 1 def from trial_clear where num is NULL) is NULL
def is NULL
else
def = @def /*given value*/

please do help me... i would appreciate it much!!
Oct 19 '07 #1
5 7927
iburyak
1,017 Expert 512MB
Try this:

Expand|Select|Wrap|Line Numbers
  1. update trial_clear set num = @count2 /* @count2 is a integer passed*/
  2. where def =
  3. case when (select top 1 def from trial_clear where num is NULL) is NULL Then null
  4. else @def  end
Good Luck.
Oct 19 '07 #2
Try this:

Expand|Select|Wrap|Line Numbers
  1. update trial_clear set num = @count2 /* @count2 is a integer passed*/
  2. where def =
  3. case when (select top 1 def from trial_clear where num is NULL) is NULL Then null
  4. else @def  end
Good Luck.



thanks for the code but still it doesn't work, i should have made my message clear... you see i've got this table..
--table---
trial_clear(num,def,def2,def3)
--these are the existing values--
1st row (NULL,q,NULL,NULL)
2nd row (NULL,w,w,NULL)
3rd row (NULL,e,e,r)
4th row (t,NULL,y)
5th row (q,w,NULL)

you see what i was supposed to do is update this table, assigning the num value with int.. starting form 1 to so on...the table i presented is just a prototype of the real table

while making my procedure i noticed that it will only work if i would say 'def is NULL' not def = NULL and that was why i was wondering if there was and if else statement inside the where clause
Oct 19 '07 #3
iburyak
1,017 Expert 512MB
Try this:

Expand|Select|Wrap|Line Numbers
  1. set rowcount 1
  2.  
  3. update trial_clear set num = @count2 /* @count2 is a integer passed*/
  4. where isnull(def, '') = ''
  5.  
  6.  
Oct 19 '07 #4
Try this:

Expand|Select|Wrap|Line Numbers
  1. set rowcount 1
  2.  
  3. update trial_clear set num = @count2 /* @count2 is a integer passed*/
  4. where isnull(def, '') = ''
  5.  
  6.  

thanks it really helped!!

But i too got another solution. I simply put the resulting rows into a cursor and it works even better than my previous code. Thanks for reminding me that there is a better way... THANK YOU!!!
Oct 20 '07 #5
ck9663
2,878 Expert 2GB
thanks it really helped!!

But i too got another solution. I simply put the resulting rows into a cursor and it works even better than my previous code. Thanks for reminding me that there is a better way... THANK YOU!!!

cursor should be your last resort...
Oct 20 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Largo SQL Tools | last post: by
9 posts views Thread by paulmac106 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.