CODEWITHSUNDEEP
sqlpostgresql
Macbernie
Macbernie

Reputation: 1323

SQL Concatenate strings without duplicate rows

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 !

Upvotes: 0

Views: 98

Answers (1)

Alastair Brown
Alastair Brown

Reputation: 1616

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

Upvotes: 2

Related Questions