SQL Concatenate strings without duplicate rows

Question

I have in my table this kind of lines:

id  email1             email2                email3             all
1   [email protected]    [email protected]       [email protected]   NULL
2   [email protected]   NULL                  [email protected]   NULL
3   [email protected]  NULL                  [email protected]   NULL
4   [email protected]   [email protected]         NULL               NULL
5   [email protected] NULL                  [email protected] NULL
6   [email protected]   [email protected]       [email protected]   NULL

With this kind of request:

UPDATE mytable SET all=concat_ws(';', email1, email2, email3);

I got:

id  email1             email2                email3             all
1   [email protected]    [email protected]       [email protected]   [email protected];[email protected];[email protected]
2   [email protected]   NULL                  [email protected]   [email protected];[email protected]
3   [email protected]  NULL                  [email protected]   [email protected];[email protected]
4   [email protected]   [email protected]         NULL               [email protected];[email protected]
5   [email protected] NULL                  [email protected] [email protected];[email protected]
6   [email protected]   [email protected]       [email protected]   [email protected];[email protected];[email protected]

But I also need to eliminate duplicates email to optain:

id  email1             email2                email3             all
1   [email protected]    [email protected]       [email protected]   [email protected];[email protected]
2   [email protected]   NULL                  [email protected]   [email protected]
3   [email protected]  NULL                  [email protected]   [email protected];[email protected]
4   [email protected]   [email protected]         NULL               [email protected];[email protected]
5   [email protected] NULL                  [email protected] [email protected]
6   [email protected]   [email protected]       [email protected]   [email protected];[email protected];[email protected]

Thanks for help !

Alastair Brown · Accepted Answer · 2016-05-26 01:09:01Z

2

This might not be the ideal solution, but you could try case ... end clauses to determine duplicates:

UPDATE mytable SET all=concat_ws(
     ';'
     , email1
     , case when email2 in (email1) then null else email2 end
     , case when email3 in (email1,email2) then null else email3 end);

answered May 26, 2016 at 1:09

Alastair Brown

1,6169 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Macbernie Over a year ago

Yea, I don't know if it is the best solution, but it works, thanks

Collectives™ on Stack Overflow

SQL Concatenate strings without duplicate rows

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related