MySQL Unique Index & Prevention of Inserting Duplicate Records

HI,

Say, I have a table in MySQL 5.1 with certain indexes that are unique. When I do an insert query, I wish to prevent record duplication & hence the indexes. The stored procedure I have created needs to be run on occasion, more than once in order to fix up any errors.

When an insert query is executed for the second time, I get an error message that the data already exits. This is naturally expected. How can  I suppress this error message from displaying?

TX
shaunwinginAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Louis01Commented:
1.
DECLARE EXIT HANDLER FOR SQLEXCEPTION  ROLLBACK;
    START TRANSACTION;
      INSERT...
      INSERT...
    COMMIT;

Open in new window


or
2.
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shaunwinginAuthor Commented:
Tx, please can you explain what each statement does in above.
0
Louis01Commented:
Refer to the Condition Handling section in the MySql reference documentation for an explanation: http://dev.mysql.com/doc/refman/5.0/en/declare-handler.html
0
shaunwinginAuthor Commented:
Please xplain the value '23000:
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000
0
Louis01Commented:
DECLARE handler_action HANDLER FOR condition_value

The handler_action can be CONTINUE, EXIT or UNDO

The condition_value can be mysql_error_code, SQLSTATE [VALUE] sqlstate_value, condition_name, SQLWARNING, NOT FOUND, SQLEXCEPTION

So, the statement
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
means that on SQLSTATE '23000', which occurs for a duplicate-key error
the execution should simply continue

You could also say
DECLARE EXIT HANDLER FOR SQLSTATE '23000'
means that on SQLSTATE '23000', which occurs for a duplicate-key error
the execution should exit without giving an error

You could also say
DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
means that on any SQL Exception or warning
the execution should exit without giving an error

Hope that clears it up?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.