MySQL showing error 1175: You are using safe update mode in updating the table

Question

I have a table in MySQL created using:

CREATE TABLE cats (
    cat_id INT AUTO_INCREMENT,
    name VARCHAR(100),
    breed VARCHAR(100),
    age INT,
    PRIMARY KEY (cat_id)
);

I have inserted below values into this table:

INSERT INTO cats(name, breed, age) 
VALUES ('Ringo', 'Tabby', 4),
       ('Cindy', 'Maine Coon', 10),
       ('Dumbledore', 'Maine Coon', 11),
       ('Egg', 'Persian', 4),
       ('Misty', 'Tabby', 13),
       ('George Michael', 'Ragdoll', 9),
       ('Jackson', 'Sphynx', 7);

But when I try to update the table using:

UPDATE cats SET breed='Shorthair' WHERE breed='Tabby';

I'm getting the error

Error Code: 1175
You are using safe update mode

I am expecting the table to be updated according to the query.

Answer 1

That error means that your MySQL session has a safe-updates option enabled.

If this option is enabled, UPDATE and DELETE statements that do not use a key in the WHERE clause or a LIMIT clause produce an error. In addition, restrictions are placed on SELECT statements that produce (or are estimated to produce) very large result sets. If you have set this option in an option file, you can use --skip-safe-updates on the command line to override it. For more information about this option, see Using Safe-Updates Mode (--safe-updates).

https://dev.mysql.com/doc/refman/8.0/en/mysql-command-options.html#option_mysql_safe-updates

You have two ways to solve this:

1. Use the primary key to update the record.

UPDATE cats SET breed='Shorthair' WHERE cat_id=<id>;

The <id> is the primary key you had set for the table.

2. Disable safe-updates option by:

SET SQL_SAFE_UPDATES = 0;

You can then execute your original UPDATE query.

Also, please make sure you enable it back after your use, by:

SET SQL_SAFE_UPDATES = 1