SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE recipients ( id INT, email varchar(100), deleted datetime ); INSERT INTO recipients (id,email) VALUES (1,'aaa@aaa.com'); INSERT INTO recipients (id,email) VALUES (2,'bbb@aaa.com'); INSERT INTO recipients (id,email) VALUES (3,'cc@aaa.com'); INSERT INTO recipients (id,email) VALUES (4,'bbb@aaa.com'); INSERT INTO recipients (id,email) VALUES (5,'aaa@aaa.com'); INSERT INTO recipients (id,email) VALUES (6,'ddd@aaa.com'); INSERT INTO recipients (id,email) VALUES (7,'aaa@aaa.com'); INSERT INTO recipients (id,email) VALUES (8,'ccc@aaa.com'); INSERT INTO recipients (id,email) VALUES (9,'aaa@aaa.com'); INSERT INTO recipients (id,email) VALUES (10,'bbb@aaa.com'); UPDATE recipients r1 JOIN recipients r2 ON r1.id > r2.id AND r1.email = r2.email SET r1.deleted = NOW(); SELECT * FROM recipients;