0

I have been getting this error message from MySQL

ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.

The query I use is this:

update custcopy set cust_contact=null where cust_id='1000000005';

The information about the MySQL I use is "Ver 8.0.32 for Win64 on x86_64 (MySQL Community Server - GPL)". And the scheme of the table I accessed is as follows:

+--------------+-----------+------+-----+---------+-------+
| Field        | Type      | Null | Key | Default | Extra |
+--------------+-----------+------+-----+---------+-------+
| cust_id      | char(10)  | NO   |     | NULL    |       |
| cust_name    | char(50)  | NO   |     | NULL    |       |
| cust_address | char(50)  | YES  |     | NULL    |       |
| cust_city    | char(50)  | YES  |     | NULL    |       |
| cust_state   | char(5)   | YES  |     | NULL    |       |
| cust_zip     | char(10)  | YES  |     | NULL    |       |
| cust_country | char(50)  | YES  |     | NULL    |       |
| cust_contact | char(50)  | YES  |     | NULL    |       |
| cust_email   | char(255) | YES  |     | NULL    |       |
+--------------+-----------+------+-----+---------+-------+

I consulted the mysql documentation and found that this statement may be helpful, but I don't quite understand why.

I need a query that would not cause that error, and disabling the SQL_SAFE_UPDATES is not an option.

yasee
  • 1
  • 2
  • 1
    Does this answer your question? [MySQL error code: 1175 during UPDATE in MySQL Workbench](https://stackoverflow.com/questions/11448068/mysql-error-code-1175-during-update-in-mysql-workbench) – maio290 Apr 06 '23 at 15:06
  • You can also temporarily work around the safe-updates restriction by using LIMIT on your UPDATE statement. See my answer to https://stackoverflow.com/questions/75186791/sql-where-clause-for-any-value/75187391#75187391 The example in that question is for DELETE, but the same works for UPDATE. – Bill Karwin Apr 06 '23 at 15:28
  • 1
    I also notice that your DESCRIBE output shows that _none_ of the columns are key columns. You should consider defining a key for your table. If your key were `cust_id`, then the safe-updates requirement would be satisfied without having to work around it. – Bill Karwin Apr 06 '23 at 15:29
  • 1
    Logically, `cust_id` should be the primary key of the table. Not only will this silence the warning, but also make the query much more efficient. – Barmar Apr 06 '23 at 15:46

0 Answers0