• UPDATE : With IGNORE , rows for which duplicate-key conflicts occur on a unique key value are not updated. Rows updated to values that would cause data conversion errors are updated to the closest valid values instead. 

## The Effect of Strict SQL Mode on Statement Execution 

The IGNORE  keyword applies to the following ignorable errors: 

ER_BAD_NULL_ERROR ER_DUP_ENTRY ER_DUP_ENTRY_WITH_KEY_NAME ER_DUP_KEY ER_NO_PARTITION_FOR_GIVEN_VALUE ER_NO_PARTITION_FOR_GIVEN_VALUE_SILENT ER_NO_REFERENCED_ROW_2 ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET ER_ROW_IS_REFERENCED_2 ER_SUBQUERY_NO_1_ROW ER_VIEW_CHECK_FAILED 

The MySQL server can operate in different SQL modes, and can apply these modes differently for different clients, depending on the value of the sql_mode  system variable. In “strict”  SQL mode, the server upgrades certain warnings to errors. 

For example, in non-strict SQL mode, inserting the string ’abc’  into an integer column results in conversion of the value to 0 and a warning: 

mysql> SET sql_mode = ’’; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t (i) VALUES(’abc’); Query OK, 1 row affected, 1 warning (0.01 sec) mysql> SHOW WARNINGS; +---------+------+--------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------+ | Warning | 1366 | Incorrect integer value: ’abc’ for column ’i’ at row 1 | +---------+------+--------------------------------------------------------+ 1 row in set (0.00 sec) 

In strict SQL mode, the invalid value is rejected with an error: 

mysql> SET sql_mode = ’STRICT_ALL_TABLES’; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t (i) VALUES(’abc’); ERROR 1366 (HY000): Incorrect integer value: ’abc’ for column ’i’ at row 1 

For more information about possible settings of the sql_mode  system variable, see Section 5.1.11, “Server SQL Modes” . 

Strict SQL mode applies to the following statements under conditions for which some value might be out of range or an invalid row is inserted into or deleted from a table: 

• ALTER TABLE 

• CREATE TABLE 

• CREATE TABLE ... SELECT 

• DELETE  (both single table and multiple table) 

• INSERT 

