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  amp  I m not able to update this value  Please  tell me what is wrong

User · Answer

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  lt  gt  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

User · 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

User · Answer

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

User · Answer

Preferences     Safe Updates     Restart server

User · Answer

SET SQL SAFE UPDATES 0    OR   Go to Edit -- gt  Preferences  Click SQL Queries tab and uncheck Safe Updates check box  Query -- gt  Reconnect to Server  Now execute your sql query

User · Answer

On WorkBench I resolved it By deactivating the safe update mode   -Edit -  Preferences -  Sql Editor then uncheck Safe update

User · 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

User · Answer

If you re having this problem in a stored procedure and you aren t able to use the key in the WHERE clause  you can solve this by declaring a variable that will hold the limit of the rows that should be updated and then use it in the update delete query  DELIMITER   CREATE PROCEDURE myProcedure   BEGIN     DECLARE the limit INT       SELECT COUNT    INTO the limit     FROM my table     WHERE my column IS NULL               UPDATE my table     SET my column   true     WHERE my column IS NULL     LIMIT the limit  END

User · Answer

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

User · Answer

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 field1      where id  lt  gt  0 and id in  select id from ids     That worked   Well golly -- if I am always adding where key  lt   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

User · Answer

In the MySQL Workbech version 6 2 don t exits the PreferenceSQLQueriesoptions  In this case it s possible use  SET SQL SAFE UPDATES 0

User · Answer

SET SQL SAFE UPDATES 0  UPDATE tablename SET columnname 1  SET SQL SAFE UPDATES 1

User · Answer

SET SQL SAFE UPDATES   0     your code SQL here  SET SQL SAFE UPDATES   1

User · Answer

just type SET SQL SAFE UPDATES   0  before the delete or update and set to 1 again SET SQL SAFE UPDATES   1

User · Answer

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   gt    Edit     gt    Preferences   - gt    SQL Editor   - gt  Uncheck  Safe Updates     Old version can   MySQL Workbench   gt   Edit    gt   Preferences    gt   SQL Queries

User · Answer

I too got the same issue but when I off  safe updates   in Edit - gt  Preferences - gt  SQL Editor - gt  Safe Updates  still I use to face the error as  quot Error code 1175 disable safe mode quot   My solution for this error is just given the primary key to the table if not given and update the column using those primary key value   Eg  UPDATE  table name  SET Empty Column    Value   WHERE  primary key column name    value

User · Answer

Follow the following steps before executing the UPDATE command  In MySQL Workbench   Go to Edit --  Preferences Click  SQL Editor  tab and uncheck  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

User · Answer

If you are in a safe mode  you need to provide id in where clause  So something like this should work   UPDATE tablename SET columnname 1 where id gt 0

User · Answer

The simplest solution is to define the row limit and execute  This is done for safety purposes

User · Answer

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

[mysql] MySQL error code: 1175 during UPDATE in MySQL Workbench

Examples related to mysql

Examples related to sql-update

Examples related to mysql-workbench