Author |
Topic |
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2013-12-03 : 03:33:20
|
select code,segment from tableAcode segmentA 1B 2A 1B 2how 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 newColumnA 1 NULLB 2 BA 1 NULLB 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' endFROM 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 newColumnA 1 NULLB 2 BA 1 NULLB 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 Bcode segment newColumnA 1 BB 2 BA 1 BB 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 Bcode segment newColumnA 1 BB 2 BA 1 BB 2 B
if you wanted to replace all to B then justselect 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=2some 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=2some 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 clauseupdate tset newColumn = 'B'from tableA twhere segment = 2 or if you want the newcolumn in the SELECTselect newColumn = case when segment = 1 then 'A' when segment = 2 then 'B' when segment = 3 then 'C' endfrom 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 newColumn1 A 1 B1 B 2 B 1 A 1 B1 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=1Unless you have other rules or condition, looks like what you want is justupdate tset 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 newColumn1 A 1 B1 B 2 B 1 A 1 B1 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=2some 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 aset newColumn = 'B'from tableA awhere exists ( select * from tableA x where x.ID = a.ID and x.segment = 2 ) KH[spoiler]Time is always against us[/spoiler] |
|
|
|