MySQL error code: 1175 during UPDATE in MySQL Workbench
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?
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.
Popular Answer
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).
Read more... Read less...
Follow the following steps before executing the UPDATE command: In MySQL Workbench
- Go to
Edit-->Preferences - Click
"SQL Editor"tab anduncheck"Safe Updates"check box Query-->Reconnect to Server// logout and then login- Now execute your SQL query
p.s., No need to restart the MySQL daemon!
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. 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.
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.
SET SQL_SAFE_UPDATES = 0;
# your code SQL here
SET SQL_SAFE_UPDATES = 1;