Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am inserting into the following table using LuaSQL with PtokaX API.

CREATE TABLE `requests` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `ctg` VARCHAR(15) NOT NULL,
    `msg` VARCHAR(250) NOT NULL,
    `nick` VARCHAR(32) NOT NULL,
    `filled` ENUM('Y','N') NOT NULL DEFAULT 'N',
    `dated` DATETIME NOT NULL,
    `filldate` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `nick_msg` (`nick`, `msg`),
    UNIQUE INDEX `ctg_msg` (`ctg`, `msg`)
)
COMMENT='Requests from users in any of the categories.'
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

Now, my problem is, when a user(represented by nick) tries to insert same request again, the UNIQUE index is checked and the script returns a false. This causes my script to fail and I have to restart the script.

Is there something I can do in the INSERT ... ON DUPLICATE KEY command so that it does nothing or at-least does NOT return an error in case of DUPLICATE KEY?

Otherwise I'd have to go for updating my dated field with the new DATETIME value.

share|improve this question

1 Answer 1

up vote 6 down vote accepted

Three ways. Either IGNORE duplicate errors:

INSERT IGNORE 
  ... ;                   -- without ON DUPLICATE KEY

or try to do a redundant update when there is a duplicate:

INSERT 
  ... 
ON DUPLICATE KEY UPDATE
  id = id ;

or check for duplicates before inserting:

INSERT INTO requests
  (id, ctg, msg, nick, filled, dated, filldate)
SELECT
  (NULL, 'urgent', 'Help!', 'Hermione', 'Y', NOW(), NOW())
FROM
  dual
WHERE NOT EXISTS
      ( SELECT *  FROM requests  WHERE (nick, msg) = ('Hermione', 'Help!') )
  AND NOT EXISTS
      ( SELECT *  FROM requests  WHERE (ctg, msg) = ('urgent', 'Help!') ) ;

A difference between the 3rd way and the first two is that when there are duplicates, the id will not be incremented. With INSERT IGNORE and INSERT ... ON DUPLICATE KEY, it will be auto incremented and since the insert will not be done, you'll have gaps in the values of id.

I should also add that your scripts should always check for errors anyway and not fail when there is one. Any query or statement can fail and return error occasionally, for various reasons. The tricks above will only save you from one kind of error.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.