How to use inserst ignore in mysql stored procedure

Question

I want to have an error trap where the data will not be inserted into the table if it already existed. I have this code but it doesn't work.

 INSERT IGNORE INTO staging_table (playlist_id,terminal_id,userid) VALUES
(
 (SELECT playlist_id FROM playlist_terminal WHERE playlist_id = i_playlistid),
 (SELECT terminal_id FROM playlist_terminal WHERE playlist_id = i_playlistid),
 (SELECT userid FROM playlist_terminal WHERE playlist_id = i_playlistid)
);

Answer 1

As documented under INSERT Syntax:

If you use the IGNORE keyword, errors that occur while executing the INSERT statement are ignored. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row still is not inserted, but no error occurs. Ignored errors may generate warnings instead, although duplicate-key errors do not.

Therefore you must define a uniqueness constraint over whichever columns determine whether a record "already exists". For example, if you only want to reject records that match on all three columns:

ALTER TABLE staging_table ADD UNIQUE KEY (playlist_id, terminal_id, userid);

Furthermore, rather than INSERT IGNORE, it is better to use INSERT ... ON DUPLICATE KEY UPDATE with an UPDATE clause that doesn't change anything (since this will only ignore duplicate key errors and avoid ignoring any other form of error - see “INSERT IGNORE” vs “INSERT … ON DUPLICATE KEY UPDATE”).

You can also simplify your INSERT statement by using INSERT ... SELECT:

INSERT INTO staging_table
  (playlist_id, terminal_id, userid)
    SELECT playlist_id, terminal_id, userid
    FROM   playlist_terminal
    WHERE  playlist_id = i_playlistid
ON DUPLICATE KEY UPDATE
  playlist_id = playlist_id
;