Perl MySQL - How do I skip updating or inserting a row if a particular field matches?

Question

I am pretty new to this so sorry for my lack of knowledge.

I set up a few tables which I have successfully written to and and accessed via a Perl script using CGI and DBI modules thanks to advice here.

This is a member list for a local band newsletter. Yeah I know, tons of apps out there but, I desire to learn this.

1- I wanted to avoid updating or inserting a row if an piece of my input matches column data in one particular column/field.

When creating the table, in phpmyadmin, I clicked the "U" (unique) on that columns name in structure view. That seemed to work and no dupes are inserted but, I desire a hard coded Perl solution so, I understand the mechanics of this.

I read up on "insert ignore" / "update ignore" and searched all over but, everything I found seems to not just skip a dupe.

The column is not a key or autoinc just a plain old field with an email address. (mistake?)

2- When I write to the database, I want to do NOTHING if the incoming email address matches one in that field.

I desire the fastest method so I can loop through their existing lists export data, (they cannot figure out the software) with no racing / locking issues or whatever conditions in which I am in obvious ignorance.

Since I am creating this from scratch, 1 and 2 may be in fact partially moot. If so, what would be the best approach?

I would still like an auto increment ID so, I can access via the ID number or loop through with some kind of count++ foreach.

My stone knife approach may be laughable to the gurus here but, I need to start somewhere.

Thanks in advance for your assistance.

Answer 1

Just do the insert, if data would make the unique column not be unique you'll get an SQL error, you should be able to trap this and do whatever is appropriate (e.g. ignore it, log it, alert user ...)

Answer 2

With the email address column declared UNIQUE, INSERT IGNORE is exactly what you want for insertion. Sounds like you already know how to do the right thing!

(You could perform the "don't insert if it already exists" functionality in perl, but it's difficult to get right, because you have to wrap the test and update in a transaction. One of the big advantages of a relational database is that it will perform constraint checks like this for you, ensuring data integrity even if your application is buggy.)

For updating, I'm not sure what an "update ignore" would look like. What is in the WHERE clause that is limiting your UPDATE to only affect the 1 desired row? Perhaps that auto_increment primary key you mentioned? If you are wanting to write, for example,

UPDATE members SET firstname='Sue' WHERE member_id = 5;

then I think this "update ignore" functionality you want might just be something like

UPDATE members SET firstname='Sue' WHERE member_id = 5
    AND email != '[email protected]';

which is an odd thing to do, but that's my best guess for what you might mean :)