Skip to content
Advertisement

Assign NULL to repeated values in different columns in SQL

I have a table like this:

id mail_1 mail_2 mail_3
1 john john_v2 NULL
2 clarisse clarisse clarisse_company
3 NULL julie NULL
4 mark markus_91 mark
5 alfred alfred alfred

And I would like to put NULLs where the mail is repeated, for example in the row 2 mail_1 and mail_2 have the same value; clarisse, and I would like to assign a NULL in mail_2. So I’m thinking in an algorithm that first fix the row and then go through the columns and check if the current value is the same as the previous. So the final table would be something like this:

id mail_1 mail_2 mail_3
1 john john_v2 NULL
2 clarisse NULL clarisse_company
3 NULL julie NULL
4 mark markus_91 NULL
5 alfred NULL NULL

Doing this with other languages is pretty easy, such Python or R, but I would like to have it done in SQL.

Any ideas? Thanks.

Advertisement

Answer

Seems you could use NULLIF:

SELECT id,
       Mail1,
       NULLIF(Mail2,Mail1) AS Mail2,
       NULLIF(NULLIF(Mail3,Mail2),Mail1) AS Mail3
FROM dbo.YourTable;

Ideally, however, you should be normalising your design. Don’t have 3 mail columns, have just one. If a “thing” can have multiple values for Mail, then you need 2 tables with a one to many relationship. If 2 “things” can have the same Mail as well, then you need 3 tables, so that you can define a many to many relationship.

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement