top button
Flag Notify
Site Registration

Any body know the query about this senario

+3 votes
61 views
Name | Gender -------------- 
A | F 
B | M 
C | F 
D | F 
E | M

From the above data, gender was wrongly entered, which means in place of F it should be M and in place of M it should F. How to update whole table with a single line sql query (don't use pl/sql block). Since, if I will update gender column one by one, then possible error would be all rows values of gender column becomes either F or M.
Final output should be

Name | Gender -------------- 
A | M 
B | F 
C | M 
D | M
 E | F 
posted Jun 18, 2015 by Shivaranjini

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

+1 vote
 
Best answer
declare @Table TABLE ( Id int, Value char(1) )
insert into @Table
select 1, 'F'
union select 2, 'F'
union select 3, 'F'
union select 4, 'M'
union select 5, 'M'
union select 6, 'M'
select * from @Table
update @Table set Value = case when Value = 'F' then 'M' when Value = 'M' then 'F' else Value End
select * from @Table​
answer Jun 18, 2015 by Manikandan J
Similar Questions
+4 votes

Table Name : Hist_table

 Shp_cd wt_grp  net_wt  tot_wt
   101   10       9      7
   102   20       8      2
   103   15       4      1

Factor_table

Fact_id fact_column factor
  1     wt_grp        2
  2     net_wt        5
  3     tot_wt        3

Note – this factor table contains rows as the column name of hist_table.
Now we have to update the Hist_table with the multiplied with the factors
For example wt_grp factor is 2 then we have to update all the wt_grp column of hist table as Hist_table.wt_grp * factor_table.factor
So the result should be

shpcd   wt_grp  net_wt  tot_wt
101     20        45    21
102     40        40    6
103     30        20    3
...