CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Asynchronous
Asynchronous

Reputation: 3977

How to insert data from Column A if Column B is Null and from Column B if Column A is Null

I am thinking I am getting close but not sure:

I have an example Table containing email address, Personal and Work. I am trying to create a new table which holds a single address.

Then insert an email address, example: If a user has a work email but no personal email, the insert work email and if no work email, insert personal email.

The following code does not work but it's just to show some of the many variations I have used:

USE EXAMPLE_DB

GO

CREATE TABLE USER_EMAIL(
 USER_NBR CHAR(1) NOT NULL
,EMAIL VARCHAR(30)NULL
)

GO

INSERT INTO USER_EMAIL(
 USER_NBR
,EMAIL
)

SELECT USER_NBR
,CASE WHEN EMAIL_ADDR IS NULL AND EMAIL_TYPE = 'Personal' THEN EMAIL_TYPE = 'Work' END EMAIL
,CASE WHEN EMAIL_ADDR IS NULL AND EMAIL_TYPE = 'Work'     THEN EMAIL_TYPE =  'Personal' END EMAIL
FROM EMAIL_DATA

Here is the table for the EMAIL_DATA:

USER_NBR   EMAIL_TYPE       EMAIL_ADDR
1          Personal 
2          Personal     [email protected]
3          Personal         [email protected]
4          Personal 
5          Personal         [email protected]
1          Work         [email protected]
2          Work         [email protected]
3          Work 
4          Work         [email protected]
5          Work

Thanks everyone!

Upvotes: 3

Views: 1134

Answers (3)

John Dewey
John Dewey

Reputation: 7093

Based on your requirement clarification, where each user has only one type or another in the source, it sounds like you could simply do this:

INSERT INTO USER_EMAIL(USER_NBR, EMAIL_ADDR)
SELECT USER_NBR, EMAIL_ADDR
FROM EMAIL_DATA
WHERE EMAIL_ADDR IS NOT NULL

Upvotes: 2

Tim Schmelter
Tim Schmelter

Reputation: 460068

This does following: If EMAIL_ADDR is null it'll look into the same table and search an email of the other email-type for the same user. Is it was you want?

INSERT INTO USER_EMAIL(
 USER_NBR
,EMAIL 
)
SELECT e.USER_NBR
     , EMAIL = COALESCE(e.EMAIL_ADDR
                    , (CASE e.EMAIL_TYPE
                        WHEN 'Work' THEN (SELECT TOP 1 EMAIL_ADDR  
                                      FROM EMAIL_DATA e1
                                      WHERE e1.USER_NBR=e.USER_NBR
                                      AND EMAIL_TYPE='Personal')
                        WHEN 'Personal' THEN (SELECT TOP 1 EMAIL_ADDR 
                                      FROM EMAIL_DATA e1
                                      WHERE e1.USER_NBR=e.USER_NBR
                                      AND EMAIL_TYPE='Work')
                    END))
FROM EMAIL_DATA e

Result with your sample data:

USER_NBR    EMAIL
1           [email protected]
2           [email protected]
3           [email protected]
4           [email protected]
5           [email protected]
1           [email protected]
2           [email protected]
3           [email protected]
4           [email protected]
5           [email protected]

Upvotes: 0

Andomar
Andomar

Reputation: 238078

Here's an example using row_number:

insert  user_email
        (user_nbr, email)
select  user_nbr
,       mail.email_addr
from    (
        select  user_nbr
        ,       email_addr
        ,       row_number() over (partition by user_nbr
                  order by
                  case 
                  when email_type = 'personal' then 1
                  when email_type = 'work' then 2
                  end) as rn
        from    email_data
        ) usr
where   usr.rn = 1

Since there are just two email types, you could also use left join twice:

insert  user_email
        (user_nbr, email)
select  user_nbr
,       coalesce(pers.email_addr, work.email_addr)
from    (
        select  distinct user_nbr
        from    email_data
        ) usr
left join 
        email_data as pers 
on      pers.user_nbr = usr.user_nbr
        and work.email_type = 'personal'
left join 
        email_data as work
on      work.user_nbr = usr.user_nbr
        and work.email_type = 'work'

Another approach that looks up the preferred email address using cross apply:

insert  user_email
        (user_nbr, email)
select  user_nbr
,       mail.email_addr
from    (
        select  distinct user_nbr
        from    email_data
        ) usr
cross apply
        (
        select  top 1 *
        from    email_data mail
        where   mail.user_nbr = usr.user_nbr
                and mail.email_addr is not null
        order by
                case 
                when email_type = 'personal' then 1
                when email_type = 'work' then 2
                end
        ) mail

Upvotes: 2

Related Questions