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
 General SQL Server Forums
 New to SQL Server Programming
 Replace method
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

peace
Constraint Violating Yak Guru

410 Posts

Posted - 12/03/2013 :  03:33:20  Show Profile  Reply with Quote
select code,segment from tableA

code segment
A 1
B 2
A 1
B 2

how can i create new column with condition if segment=2 then B apple to all.

I tried using case when but those which is not B appear NULL.

code segment newColumn
A 1 NULL
B 2 B
A 1 NULL
B 2 B

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 12/03/2013 :  03:43:07  Show Profile  Reply with Quote
SELECT newColumn = case when segment = 2 then 'B' end
FROM tableA



KH
Time is always against us

Go to Top of Page

peace
Constraint Violating Yak Guru

410 Posts

Posted - 12/03/2013 :  03:55:59  Show Profile  Reply with Quote
i tried but it return:

code segment newColumn
A 1 NULL
B 2 B
A 1 NULL
B 2 B
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 12/03/2013 :  04:15:38  Show Profile  Reply with Quote
is that what you wanted ?


KH
Time is always against us

Go to Top of Page

peace
Constraint Violating Yak Guru

410 Posts

Posted - 12/03/2013 :  04:33:03  Show Profile  Reply with Quote
wanted to replace all to B

code segment newColumn
A 1 B
B 2 B
A 1 B
B 2 B
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 12/03/2013 :  04:44:45  Show Profile  Reply with Quote
quote:
if segment=2 then B


is that what you wanted ?


KH
Time is always against us

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 12/03/2013 :  04:46:49  Show Profile  Reply with Quote
quote:
Originally posted by peace

wanted to replace all to B

code segment newColumn
A 1 B
B 2 B
A 1 B
B 2 B



if you wanted to replace all to B then just

select code, segment, newColumn = 'B'
from   tableA



KH
Time is always against us

Go to Top of Page

peace
Constraint Violating Yak Guru

410 Posts

Posted - 12/03/2013 :  04:58:50  Show Profile  Reply with Quote
theres condition where segment=2 will replace the code which segment=2
some other id will have different code.

thats why i did the case when but seems like it only will replace when segment=2 while other will be NULL.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 12/03/2013 :  05:18:34  Show Profile  Reply with Quote
quote:
Originally posted by peace

theres condition where segment=2 will replace the code which segment=2
some other id will have different code.

thats why i did the case when but seems like it only will replace when segment=2 while other will be NULL.



Are you updating the table or just want the newcolumn when you select from the table ?

If you are updating the table, add the condition in the WHERE clause

update t
set    newColumn = 'B'
from   tableA t
where  segment = 2


or if you want the newcolumn in the SELECT

select newColumn = case when segment = 1 then 'A'
                        when segment = 2 then 'B'
                        when segment = 3 then 'C'
                        end
from   tableA




KH
Time is always against us

Go to Top of Page

peace
Constraint Violating Yak Guru

410 Posts

Posted - 12/03/2013 :  21:12:56  Show Profile  Reply with Quote
Im trying to add in new column where segment =2 then new column will apple for all is B.

ID code segment newColumn
1 A 1 B
1 B 2 B
1 A 1 B
1 B 2 B
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 12/04/2013 :  00:18:54  Show Profile  Reply with Quote
You are confusing me. What you describe and the required result does not tally.

You said "where segment =2 then new column will apple for all is B." but the required result that you posted has B where segment=1

Unless you have other rules or condition, looks like what you want is just

update t
set    newColumn = 'B'
from   tableA t



KH
Time is always against us

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 12/04/2013 :  00:22:59  Show Profile  Reply with Quote
quote:
Originally posted by peace

Im trying to add in new column where segment =2 then new column will apple for all is B.

ID code segment newColumn
1 A 1 B
1 B 2 B
1 A 1 B
1 B 2 B



Oh now you have a new column ID appeared. Any significant for this ID column in affected the result ?


KH
Time is always against us

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 12/04/2013 :  00:25:38  Show Profile  Reply with Quote
quote:
Originally posted by peace

theres condition where segment=2 will replace the code which segment=2
some other id will have different code.

thats why i did the case when but seems like it only will replace when segment=2 while other will be NULL.




after re-reading the entire thread for countless times, this earlier reply starts to make some sense. I am guessing this is what you want ?

update a
set    newColumn = 'B'
from   tableA a
where  exists
       (
           select *
           from   tableA x
           where  x.ID = a.ID
           and    x.segment = 2
       )


KH
Time is always against us


Edited by - khtan on 12/04/2013 00:26:08
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.09 seconds. Powered By: Snitz Forums 2000