IGNORE
Example
Section titled “Example”INSERT IGNORE INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date)VALUES ('9999', '1965-02-02', 'John', 'Doe', 'M', '2002-07-30');Output
Section titled “Output”Query OK, 0 rows affected (0.00 sec)Explanation
Section titled “Explanation”The INSERT IGNORE statement prevents an error from occurring and skipping the row that would cause the error when inserting data into a table. In the provided example, if ‘9999’ already exists in the ‘emp_no’ column in the ‘employees’ table, the database engine would omit this row and carry on processing any subsequent rows. The output displays ‘Query OK, 0 rows affected’ meaning no row was inserted due to the IGNORE clause.
Example
Section titled “Example”INSERT OR IGNORE INTO employees(id, name, dept_id)VALUES(1, 'John', 101);Output
Section titled “Output”If id 1 already exists, there will not be any output and no new entry will be recorded in the table.
Explanation
Section titled “Explanation”The INSERT OR IGNORE command in SQL checks during the insertion of new data. If the data already exists, it will simply ignore and not insert the new data into the table. The use of IGNORE prevents errors that occur due to duplications, or violations of unique constraints. This sample query attempts to insert record with id 1, name ‘John’, and dept_id 101 into the employees table. If a record with id 1 already exists, SQL ignores the insertion.