CODEWITHSUNDEEP
sqloracle-databaseselectsql-update
Ravi
Ravi

Reputation: 31407

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

Upvotes: 4

Views: 2996

Answers (5)

Sunny
Sunny

Reputation: 99

you can try this:-

update [table] a 
set Gender=(select case when gender='F' then 'M' else 'F' end from [table] b
where a.name=b.name)

above query will match the names and will update gender accordingly.

Upvotes: 0

Amit Singh
Amit Singh

Reputation: 8109

Try this.. 

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);

SQL FIDDLE DEMO

Upvotes: 3

Vishwanath Dalvi
Vishwanath Dalvi

Reputation: 36621

update table_name
set    gender =  case when gender = 'F' then 'M' 
                      when gender = 'M' then 'F'
                  end

SQL works on Set theory principles, so updates are happening in parallel, you don't need Temporary storage to store the values before overwriting like we do in other programming language while swapping two values.

Upvotes: 3

A.B.Cade
A.B.Cade

Reputation: 16905

The right way to do such an update is as Amit singh first answered.

But if you really want to have a select statement in your update (have know idea why), then you can do something like this:

update table1 t
set Gender = (select case when i.Gender = 'F' Then 'M' else 'F' end 
                from table1 i 
                where i.Name = t.Name);

Here is a sqlfiddle demo

Upvotes: 1

Christian Farinella
Christian Farinella

Reputation: 1

sql server example but same applies

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

Upvotes: 0

Related Questions