ON DUPLICATE KEY UPDATE

When processing addresses for postcard mailings we get a lot of addresses that don’t pass the VistaPrint address validation process. Fortunately, VistaPrint has a button that lets you download the addresses. What makes it easy for me to mark the bad addresses in my database is that I don’t use a salutation and instead put the customer number in the salutation field. I strip out all of the address information and keep just the customer ID. I then massage the IDs so that they look like this.


INSERT INTO `customers` (id, BadVistaPrint) VALUES 
(15771, 1), 
...
(16248, 1), 
(16249, 1)
ON DUPLICATE KEY UPDATE BadVistaPrint=VALUES(BadVistaPrint)

What this code does is look for customer ID and then update the BadVistaPrint value. Not sure why it uses the ON DUPLICATE KEY syntax. Note that the last row before the ON DUPLICATE KEY does not have a comma.

I paste this into the SQL section of phpMyAdmin and I my database is now updated with bad addresses. I can manually look for easy to fix addresses e.g. missing numbers, zip codes, etc. and look them up. There are lots of addresses that are mailable but not in the VistaPrint database e.g. University departments and rural route addresses and I mail them separately.

I also use the USPS zip code finder to validate addresses.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.