MySQL error code: 1175 during UPDATE in MySQL Workbench

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?

 Answers

It looks like your MySql session has the safe-updates option set. This means that you can't update or delete records without specifying a key (ex. primary key) in the where clause.

Try:

SET SQL_SAFE_UPDATES = 0;

Or you can modify your query to follow the rule (use primary key in where clause).

---

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

---

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.

---

SET SQL_SAFE_UPDATES=0;

OR

Go to Edit --> Preferences

Click SQL Queries tab and uncheck Safe Updates check box

Query --> Reconnect to Server

Now execute your sql query

---

I found the answer. The problem was that I have to precede the table name with the schema name. i.e, the command should be:

UPDATE schemaname.tablename SET columnname=1;

Thanks all.

---

Since the question was answered and had nothing to do with safe updates, this might be the wrong place; I'll post just to add information.

I tried to be a good citizen and modified the query to use a temp table of ids that would get updated:

create temporary table ids ( id int )
    select id from prime_table where condition = true;
update prime_table set field1 = '' where id in (select id from ids);

Failure. Modified the update to:

update prime_table set field 1 = '' where id <> 0 and id in (select id from ids);

That worked. Well golly -- if I am always adding where key <> 0 to get around the safe update check, or even set SQL_SAFE_UPDATE=0, then I've lost the 'check' on my query. I might as well just turn off the option permanently. I suppose it makes deleting and updating a two step process instead of one.. but if you type fast enough and stop thinking about the key being special but rather as just a nuisance..

---

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');