0

I've found a lot of similar questions, but not exactly what I need. I want to make a SQL command to find all duplicate email rows and update the notes of them with 'do not email' if it is written in the note of one of them.

In the example below I would like to update row 2,4 and 6 with the words "do not email" in the note.

ID | email | note
--------------------
1 | A | do not email
2 | B |
3 | C |
4 | A |
5 | D |
6 | A |
7 | B | do not email

Does anyone have any hints?

2

This will update all rows in the target table that match an email address in the same table that has been marked as 'do not email'

update a
   set a.note = 'do not email'
  from tbl as a
  join (select distinct email
          from tbl 
         where note = 'do not email'
       ) as b 
    on a.email = b.email
 where a.note <> 'do not email' 
    or a.note is null;
0

I think this will solve your problem.

DECLARE @table TABLE
(
    Id INT IDENTITY(1,1)
    , Email VARCHAR(500)
    , Note VARCHAR(200) NULL
);

INSERT INTO @table
        (Email, Note)
VALUES  ('A', 'do not email'), ('B', NULL), ('C', NULL), ('A', NULL), ('D',NULL), ('A',NULL), ('B','do not email');

SELECT * FROM @table;

-- Actual Data
--------------------
-- Id | Email | Note
--------------------
--  1       A   do not email
--  2       B   NULL
--  3       C   NULL
--  4       A   NULL
--  5       D   NULL
--  6       A   NULL
--  7       B   do not email

MERGE INTO @table AS Target
USING (SELECT DISTINCT      
    Email ,
    Note 
FROM @table 
WHERE Note = 'do not email'
) AS Source 
    ON Target.[Email] = Source.[Email]          
WHEN MATCHED THEN
UPDATE  
SET 
    Target.[Note] = Source.[Note];


SELECT * FROM @table        

-- After Update
--------------------
-- Id | Email | Note
--------------------
--  1       A   do not email
--  2       B   do not email
--  3       C   NULL
--  4       A   do not email
--  5       D   NULL
--  6       A   do not email
--  7       B   do not email
0

Here is another method, which would work in SQL Server 2005 or later version:

WITH cte AS
(
  SELECT
    note,
    do_not_email_note = MAX(CASE note WHEN 'do not email' THEN note END)
                        OVER (PARTITION BY email)
)
UPDATE
  cte
SET
  note = do_note
WHERE
  do_not_email_note = 'do not email'
  AND NOT EXISTS (SELECT note INTERSECT SELECT do_not_email_note)
;

The method would also work in any other DBMS that supports window functions and updatable CTEs, but you would need to tweak the script a little. In particular, you would need to change the aliasing style for do_not_email_note from alias = expression to expression AS alias and probably rewrite this predicate:

NOT EXISTS (SELECT note INTERSECT SELECT do_not_email_note)

as something like this:

(note IS NULL or note <> do_not_email_note)

(Note the brackets around the disjunction, they are important for the whole condition to work correctly.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.