CODEWITHSUNDEEP
sql
Sujoy
Sujoy

Reputation: 117

SQL: Update table with alternative values from same table if values are missing

I have a table with data some thing like this:

-------------------------------------------------------
member id | Email_address | alternate_Email_Address 
------------------------------------------------------
001       | [email protected]  | [email protected]
001       |               | [email protected]
001       |               | [email protected]
002       | [email protected]  | [email protected]
002       |               | [email protected]

I want to run a SQL which will copy the email address for all where all alternate_Email_Address is same.

After running the SQL I want the table something like this:

-------------------------------------------------------
member id | Email_address | alternate_Email_Address 
------------------------------------------------------
001       | [email protected]  | [email protected]
001       | [email protected]  | [email protected]
001       | [email protected]  | [email protected]
002       | [email protected]  | [email protected]
002       | [email protected]  | [email protected]

Upvotes: 1

Views: 77

Answers (2)

GHWP
GHWP

Reputation: 77

UPDATE <TABLE> 
SET    email_address = 
       (SELECT email_address 
        FROM   <TABLE> lu 
        WHERE  lu.alternate_email_address = <TABLE>.alternate_email_address 
               AND (email_address IS NOT NULL AND length(lu.email_address) > 1) limit 1) 
WHERE  email_address IS NULL

Replace the where with WHERE email_address = "" if it's empty string, not NULL.

Make sure you have an index on alternate_email_address. Use limit 1 rather than an aggregate function if possible for performance reasons.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270793

If I understand what you want, then a method that uses ANSI standard SQL is:

update t
    set email_address = (select max(t2.email_address)
                         from t t2
                         where t2.alternate_Email_Address = t.alternate_Email_Address and
                               t2.email_address is not null
                        )
    where email_address is null;

Upvotes: 2

Related Questions