Advertisement
Advertisement


MySQL error code: 1175 during UPDATE in MySQL Workbench


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?

2013/01/10
1
840
1/10/2013 4:40:34 AM

Accepted Answer

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.

2013/07/25
17
7/25/2013 5:49:02 AM


Follow the following steps before executing the UPDATE command: In MySQL Workbench

  1. Go to Edit --> Preferences
  2. Click "SQL Editor" tab and uncheck "Safe Updates" check box
  3. Query --> Reconnect to Server // logout and then login
  4. Now execute your SQL query

p.s., No need to restart the MySQL daemon!

2018/04/05

SET SQL_SAFE_UPDATES=0;
UPDATE tablename SET columnname=1;
SET SQL_SAFE_UPDATES=1;
2015/03/05

No need to set SQL_SAFE_UPDATES to 0, I would really discourage it to do it that way. SAFE_UPDATES is by default on for a REASON. You can drive a car without safety belts and other things if you know what I mean ;) 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 (ALWAYS) updated as you expect.

2019/02/05

All that's needed is: Start a new query and run:

SET SQL_SAFE_UPDATES = 0;

Then: Run the query that you were trying to run that wasn't previously working.

2016/12/08

SET SQL_SAFE_UPDATES = 0;

# your code SQL here

SET SQL_SAFE_UPDATES = 1;