A few weeks in the past, I talked concerning the classes I realized whereas sending 7M emails utilizing ColdFusion. In that put up, I discussed that I wanted to retailer a cache of electronic mail validation codes as a way to keep away from sending an electronic mail to any tackle that was identified to be invalid. However, an invalid electronic mail tackle is not the one purpose to skip a given ship. If a person explicitly unsubscribes from a broadcast stream, I have to omit the given person within the subsequent ship (or my SMTP supplier—Postmark—will mark the e-mail as bounced).
When a person explicitly unsubscribes from the published stream, I replace their electronic mail validation report to retailer the worth, suppressed. However, after all, their electronic mail tackle is technically legitimate. Which suggests, if we ever run their electronic mail by means of the validation course of once more (similar to by way of NeverBounce or Electronic mail Listing Confirm), it could come again as okay or legitimate.
As a way to deal with the suppressed project as a one-way door, I wanted to ensure that as soon as an electronic mail validation was marked suppressed, it would by no means be marked as the rest. To do that, I needed to replace my SQL assertion to conditionally retailer the brand new worth if and provided that the present worth was not suppressed.
To discover this logic, let’s first take a look at the database desk construction:
CREATE TABLE `email_verification` (
`electronic mail` varchar(255) NOT NULL,
`textCode` varchar(50) NOT NULL,
`updatedAt` datetime NOT NULL,
PRIMARY KEY (`electronic mail`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Right here, we’re utilizing the electronic mail as the first key and the textCode as the e-mail verification worth. Populating this desk was going to be an iterative course of; and, the verification codes have been going to vary over time. As such, my INSERT wanted to deal with updates on electronic mail duplication (ie, on main key collision).
The SQL for such a multi-pass INSERT appears to be like like this:
SET @electronic mail="ben@bennadel.com";
SET @textCode="okay";
INSERT INTO
email_verification
SET
electronic mail = @electronic mail,
textCode = @textCode,
updatedAt = UTC_TIMESTAMP()
ON DUPLICATE KEY UPDATE
textCode = VALUES( textCode ),
updatedAt = VALUES( updatedAt )
;
On this SQL assertion, I am utilizing the ON DUPLICATE KEY UPDATE clause as a way to replace any current row with an identical electronic mail tackle (which, bear in mind, is getting used as the first key). Within the context of the ON DUPLICATE KEY UPDATE clause, the VALUES() operate returns the worth that would have been used throughout the INSERT had there been no key-collision.
With this SQL, I can run the INSERT...ON DUPLICATE KEY UPDATE as many occasions as I like; and the textCode column will maintain getting up to date for the given electronic mail tackle:
In fact, as soon as the textCode column is designated as suppressed, I by no means need it to vary. As a way to do that, my UPDATE project has to take a look at the current worth and conditionally override it. Fortunately, the UPDATE clause can reference any current column worth throughout the current row by title. Which suggests, I can use the IF() operate to return the current worth whether it is presently suppressed; or, to return the VALUES() worth whether it is the rest.
SET @electronic mail="ben@bennadel.com";
SET @textCode="okay";
INSERT INTO
email_verification
SET
electronic mail = @electronic mail,
textCode = @textCode,
updatedAt = UTC_TIMESTAMP()
ON DUPLICATE KEY UPDATE
textCode = IF(
( textCode="suppressed" ),
textCode, -- Return the prevailing worth (suppressed)
VALUES( textCode ) -- Return the brand new worth.
),
updatedAt = VALUES( updatedAt )
;
To be clear, throughout the ON DUPLICATE KEY UPDATE clause, the expression textCode refers back to the current worth throughout the current row. However, the expression VALUES(textCode) refers back to the new worth that may have been inserted. And, as soon as the textCode column is about to suppressed, it might probably by no means return to the rest:
Through the use of the IF() operate throughout the ON DUPLICATE KEY UPDATE clause, I can make sure that as soon as the textCode column is “suppressed”, any subsequent execution of this question will turn into a no-op. That stated, I am electing to all the time replace the updatedAt column on each execution as a method to supply inside suggestions to our workforce.
I might have damaged this complete algorithm up into two separate SQL queries: a SELECT question adopted by a conditional INSERT or UPDATE question. Nonetheless, this little bit of workflow operates over tens of millions of electronic mail tackle. And, if I can minimize the variety of SQL queries in half (from 2 to 1), this has a really significant impression on how briskly the general workflow can execute.
Need to use code from this put up?
Try the license.
