CODEWITHSUNDEEP
sqloracle-database
user2542118
user2542118

Reputation: 11

how to update the column of a table by find duplicate row record

I have a table containing 3 columns.

Id    email1    email2
01   [email protected]   
01                [email protected]
01   [email protected]
02   [email protected]
03                [email protected]
03   [email protected]
04   [email protected]    [email protected]

I want to update the same table having expected output/result as

Id    email1       email2
01    [email protected]    [email protected]
01    [email protected]
02    [email protected]
03    [email protected]    [email protected]
04    [email protected]

Is there any way to create a view which can select the data and display the expected output/result?

Example:

Id    email1       email2
01    [email protected]    
01                 [email protected]
01    [email protected]
01                 [email protected]
01    [email protected]
01    [email protected]
01                  [email protected]
01    [email protected]

My output should come like

Id     email1     email2
01     [email protected]  [email protected]
01     [email protected]  [email protected]
01     [email protected]
01     [email protected] 
01     [email protected]  [email protected]

basically my requirement is:the ID and the either of the eamils get updated or i want to present it on the condtion which exists as:

case 1

Id   enmail     email2
01   [email protected]  
01              [email protected]

or case 2

Id    email1    email2
01              [email protected]
01    [email protected]

as, My expected o/p should be like given below in either of the case

case 1 o/p

Id   email1     email2
01   [email protected]  [email protected]

case 2 o/p

Id    email1      email2
01    [email protected]   [email protected]

I hope.. my requirement is clear now.

Upvotes: 0

Views: 123

Answers (1)

Ben
Ben

Reputation: 52863

Your problem is that the second column is non-unique when joined back to the first. So, you need to make it unique by, for instance, using the analytic function ROW_NUMBER()

select a.id, a.email1, b.email2
  from ( select id, email1
              , row_number() over (partition by id order by 1) as r
           from table1
          where email1 is not null
                ) a
  left outer join 
       ( select id, email2
              , row_number() over (partition by id order by 1) as r
           from table1
          where email2 is not null
                ) b
   on a.id = b.id
  and a.r = b.r
order by a.id, a.email1, b.email2

SQL Fiddle

This is a slightly curious way to both store and represent data. It might make more sense to have a single e-mail column, which you insert all your e-mails in. You can then just select everything.

Upvotes: 1

Related Questions