MySQL error code: 1175 during UPDATE in MySQL Work

Question

I'm trying to update the column visited to give it the value 1. I use MySQL workbench, and I'm writing the statement in the SQL editor from inside the workbench. I'm writing the following command:

UPDATE tablename SET columnname=1;

It gives me the following error:

You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option ....

I followed the instructions, and I unchecked the safe update option from the Edit menu then Preferences then SQL Editor. The same error still appear & I'm not able to update this value. Please, tell me what is wrong?

Answer 1

This is for Mac, but must be same for other OS except the location of the preferences.

The error we get when we try an unsafe DELETE operation

On the new window, uncheck the option Safe updates

Then close and reopen the connection. No need to restart the service.

Now we are going to try the DELETE again with successful results.

So what is all about this safe updates? It is not an evil thing. This is what MySql says about it.

Using the --safe-updates Option

For beginners, a useful startup option is --safe-updates (or --i-am-a-dummy, which has the same effect). It is helpful for cases when you might have issued a DELETE FROM tbl_name statement but forgotten the WHERE clause. Normally, such a statement deletes all rows from the table. With --safe-updates, you can delete rows only by specifying the key values that identify them. This helps prevent accidents.

When you use the --safe-updates option, mysql issues the following statement when it connects to the MySQL server:

SET sql_safe_updates=1, sql_select_limit=1000, sql_max_join_size=1000000;

It is safe to turn on this option while you deal with production database. Otherwise, you must be very careful not accidentally deleting important data.

Answer 2

SET SQL_SAFE_UPDATES=0;
UPDATE tablename SET columnname=1;
SET SQL_SAFE_UPDATES=1;

Answer 3

No need to set SQL_SAFE_UPDATES to 0, I would really discourage it to do it that way. Just add in the WHERE clause a KEY-value that matches everything like a primary-key comparing to 0, so instead of writing:

UPDATE customers SET countryCode = 'USA'
    WHERE country = 'USA';               -- which gives the error, you just write:

UPDATE customers SET countryCode = 'USA'
    WHERE (country = 'USA' AND customerNumber <> 0); -- Because customerNumber is a primary key you got no error 1175 any more.

Now you can be assured every record is updated like you expect.

Answer 4

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

Turn OFF "Safe Update Mode" temporary

SET SQL_SAFE_UPDATES = 0;
UPDATE options SET title= 'kiemvieclam24h' WHERE url = 'http://kiemvieclam24h.net';
SET SQL_SAFE_UPDATES = 1;

Turn OFF "Safe Update Mode" forever

Mysql workbench 8.0:

MySQL Workbench => [ Edit ] => [ Preferences ] -> [ SQL Editor ] -> Uncheck "Safe Updates"

Old version can:

MySQL Workbench => [Edit] => [Preferences] => [SQL Queries]

Answer 5

In the MySQL Workbech version 6.2 don't exits the PreferenceSQLQueriesoptions.

In this case it's possible use: SET SQL_SAFE_UPDATES=0;

Answer 6

True, this is pointless for the most examples. But finally, I came to the following statement and it works fine:

update tablename  set column1 = '' where tablename .id = (select id from tablename2 where tablename2.column2 = 'xyz');