Interview : update table values using select statement
Interviewer asked me one question, which seems very easy, but I couldn't figure out, how to solve this
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
Update TableName Set Gender=Case when Gender='M' Then 'F' Else 'M' end
On OP request..update using Select...
Update TableName T Set Gender=( Select Gender from TableName B where T.Gender!=B.Gender and rownum=1);