How would I write this SQL script?

Question

I have a dev database that I periodically rebuild from a data dump of the prod database. After doing the initial rebuild, I have a cleanup script that I run that does a few things, notably changing all emails to a dev address so our customers don't get spammed inadvertently when we develop. This worked fine until I changed the structure of our db so that each customer can have multiple emails, with a unique constraint disallowing the same email multiple times per customer. Now I am trying to rewrite my cleanup script to change all emails to "[email protected]" but it's failing because of the unique constraint.

I don't want remove the unique constraint on my dev db for obvious reasons. I need to figure out a method to assign fake but unique emails for the customers that have multiple contacts.

Here's my data structure:

CREATE TABLE contact (
    contactid decimal(22,0) DEFAULT '0' NOT NULL,
    ...,
    PRIMARY KEY (contactid)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE contact_email (
    contactemailid decimal(22,0) DEFAULT '0' NOT NULL,
    contactid decimal(22,0) DEFAULT '0' NOT NULL,
    emailaddress varchar(255) COLLATE latin1_bin NOT NULL,
    PRIMARY KEY (contactemailid),
    CONSTRAINT ce_contactid_fk FOREIGN KEY (contactid) REFERENCES contact (contactid) ON DELETE NO ACTION ON UPDATE NO ACTION,
    INDEX ce_contactid_fk (contactid),
    CONSTRAINT ce_uniquecombokey UNIQUE KEY (contactid, emailaddress)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE merchant_contact (
    merchantcontactid decimal(22,0) DEFAULT '0' NOT NULL,
    merchantacctid decimal(22,0) DEFAULT '0' NOT NULL,
    contactid decimal(22,0) DEFAULT '0' NOT NULL,
    billingcontact tinyint(1) default '0' NOT NULL,
    PRIMARY KEY (merchantcontactid),
    CONSTRAINT mc_contactid_fk FOREIGN KEY (contactid) REFERENCES contact (contactid) ON DELETE NO ACTION ON UPDATE NO ACTION,
    INDEX mc_contactid_fk (contactid),
    CONSTRAINT mc_uniquecombokey UNIQUE KEY (merchantacctid, billingcontact)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Before I allowed multiple emails per customer, this part of my cleanup script was easy:

update contact_email set emailaddress = '[email protected]';

Now it fails because of the constraint. I get an Error Code: 1062, SQL State: 23000 (integrity constraint violation).

Any suggestions? Thanks in advance. I'm going to continue to hammer on it myself, but would appreciate suggestions/ideas from experienced SQL scripters.

ETA: Thanks for the ideas so far. One thing I left out is that the fake address that I currently set them all to, actually does come to me, so I can see the email when I am testing. I am asking our ops group if something like 'dev-{contactemailid}@abc.com' could be configured to all come to me, but I'm not too optimistic.

Leave the username part the same and change only the domain to something Else. Or better yet just change the tld such as .com to .dev that should be fairly unique. — Doon
– Doon, Commented Jul 30, 2012 at 15:42

Sebas · Accepted Answer · 2012-07-30 15:41:20Z

2

update contact_email set emailaddress = CONCAT(contactemailid, '[email protected]')

answered Jul 30, 2012 at 15:41

Sebas

21.6k9 gold badges60 silver badges109 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Spencer Kormos Over a year ago

I was going to say [email protected], but this works well too.

barclay Over a year ago

I like this because it's the simplest and guaranteed unique. If I need to see the output of an email while developing, I can change the specific merchant one to my own temporarily.

Collectives™ on Stack Overflow

How would I write this SQL script?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related