Replace method - 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

General SQL Server Forums

New to SQL Server Programming

Replace method

Author

Topic

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-12-03 : 03:33:20

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)

17689 Posts

Posted - 2013-12-03 : 03:43:07

[code]SELECT newColumn = case when segment = 2 then 'B' end
FROM tableA[/code]

KH
[spoiler]Time is always against us[/spoiler]

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-12-03 : 03:55:59

i tried but it return:

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-12-03 : 04:15:38

is that what you wanted ?

KH
[spoiler]Time is always against us[/spoiler]

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-12-03 : 04:33:03

wanted to replace all to B

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-12-03 : 04:44:45

quote:
if segment=2 then B

is that what you wanted ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-12-03 : 04:46:49

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
[spoiler]Time is always against us[/spoiler]

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-12-03 : 04:58:50

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.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-12-03 : 05:18:34

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
[spoiler]Time is always against us[/spoiler]

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-12-03 : 21:12:56

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-12-04 : 00:18:54

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
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-12-04 : 00:22:59

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
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-12-04 : 00:25:38

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
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources