11

I am trying to run an update query where actual id looks like 1273106/2 using the following query but running into below error,can anyone help how to fix it?

UPDATE ids.id_submit_table SET picked_bit='1' 
        WHERE (id like '1273106')

Row looks like below

126 1273106/2   AL.BH.1.1.1 SU_CNSS_BT_FM_AL.BH.1.1.1 username  0   0

Error:-

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 Queries and reconnect.   0.000 sec
  • 1
    This does not seem like a python question, rater an SQL question. The error message seems quite clear. Your data base is in `safe update mode` and does not allow you to select records with `WHERE (id like ...)`. You must either select the record from a KEY column or change the database mode. Hint: Perhaps you can first get the key for the record in a select statement, and then do the update using the value from the key column? – larsr Jun 16 '15 at 21:10
  • sorry ,meant to add mysql,corrected now –  Jun 16 '15 at 21:11
  • Don't use `LIKE` when you mean an exact match (which is precisely what you mean here). Use `=` instead. You don't want to update rows with IDs LIKE that value - you want to update a single row where the ID is EXACTLY that value, and your SQL should do so. – Ken White Jun 16 '15 at 21:56
  • Does this answer your question? [MySQL error code: 1175 during UPDATE in MySQL Workbench](https://stackoverflow.com/questions/11448068/mysql-error-code-1175-during-update-in-mysql-workbench) – Farhan May 15 '23 at 10:57

5 Answers5

22

This error appears because safe update mode requires key to update/delete with where clause, you can use

SET SQL_SAFE_UPDATES=0;

Before executing the query or if you want to avoid this situation in future please make sure update/delete by using key with where clause

Rahul Sharma
  • 241
  • 2
  • 6
11

try this for fix problem.

To permanently disable safe update mode in MySQL Workbench 8.0 you should do the following:

Go to Edit --> Preferences.

Click "SQL Editor" tab and uncheck "Safe Updates" (rejects UPDATEs and DELETEs with no restrictions) check box.

Query --> Reconnect to Server.

pankaj
  • 1
  • 17
  • 36
2

This is MySQL Workbench issue, not MySQL itself. You can try via any other MySQL client or you can toggle safe queries off in Workbench Preferences.

ptkoz
  • 2,388
  • 1
  • 20
  • 28
1

click on edit and find Preferences. find SQL editor click on it, go to the bottom, and uncheck click on ok.

now goto server, find the startup and shut down, stop the server and start again

rushikesh veeramally
  • 11
  • 1
0

This is a MySQL Workbench issue.

Instead of executing the query in workBench, connect to MySQL using the CLI client and run your Update/Delete query there.

The change will also be reflected in WorkBench.

Lucio Mollinedo
  • 2,295
  • 1
  • 33
  • 28
Yl okeshWAR
  • 1
  • 2
    As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Mar 05 '22 at 02:56
  • This worked for me. I didn't like the hacky answers suggesting disabling FOREIGN_KEY_CHECKS or SQL_SAFE_UPDATES. I would like to know why it works in the CLI client and not in Workbench, though. – Lucio Mollinedo Mar 29 '23 at 05:23