11

I have a MySql query, which is given below:

UPDATE signup SET lastname='Lastname', password='123'
WHERE firstname='Firstname';

I am using MySql Workbench to execute the query.

But it's not updating the row and shows this 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 in Preferences -> SQL Editor and reconnect.

trincot
  • 317,000
  • 35
  • 244
  • 286
Jack Ferzi
  • 157
  • 1
  • 1
  • 12
  • 3
    "showing error" => which one ? – Blag Nov 28 '15 at 12:36
  • my error is - 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. – Jack Ferzi Nov 28 '15 at 12:40
  • 1
    please have a look on similar questionhttp://stackoverflow.com/questions/11448068/mysql-error-code-1175-during-update-in-mysql-workbench – Hisham Nov 28 '15 at 12:44
  • Possible duplicate of http://stackoverflow.com/questions/11448068/mysql-error-code-1175-during-update-in-mysql-workbench – Blag Nov 28 '15 at 12:45

5 Answers5

31

In mysql workbench the safe mode is enabled by default, so if your WHERE clause doesn't have a key it will prevent running the query. Try disabling that using these steps -

Edit > Preferences > Sql Editor > uncheck the "Safe Updates"

Note - try reconnecting the server (Query > Reconnect to Server) and than run your query again.

Manoj Salvi
  • 2,639
  • 1
  • 17
  • 21
10

MySQL helps you particularly avoid updating/deleting multiple rows in one shot. To achieve that, it doesn't allow you to run UPDATE queries without passing the ID parameter. This is called as the SAFE UPDATES mode.

As said by @ManojSalvi, you can set it permanently from the settings.

In case you want to temporarily disable the SAFE UPDATE mode, you can try the following:-

SET SQL_SAFE_UPDATES = 0;
UPDATE signup SET lastname='Lastname', password='123'
WHERE firstname='Firstname';
SET SQL_SAFE_UPDATES = 1;
Sarath Chandra
  • 1,850
  • 19
  • 40
1

[edit] @ManojSalvi got it, workbench related

MySQL error code: 1175 during UPDATE in MySQL Workbench

Work fine for me...

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE t
    (`firstname` varchar(6), `lastname` varchar(14), `password` varchar(3))
;

INSERT INTO t
    (`firstname`, `lastname`, `password`)
VALUES
    ('Pramod', 'Alfred', '***'),
    ('test', 'hello h.', '***')
;
UPDATE t SET lastname='Alfred Schmidt', password='123' WHERE firstname='Pramod';

Query 1:

select * from t

Results:

| firstname |       lastname | password |
|-----------|----------------|----------|
|    Pramod | Alfred Schmidt |      123 |
|      test |       hello h. |      *** |
Community
  • 1
  • 1
Blag
  • 5,818
  • 2
  • 22
  • 45
  • as he already explained it is not working in the workbench, it has nothing to do with the query itself – davejal Nov 28 '15 at 13:04
  • @davejal He only give us the error message **after** my post, so I was not able to find it was workbench related, after that I've make [a comment](http://stackoverflow.com/questions/33971357/update-query-not-work-in-mysql-workbench/33971406?noredirect=1#comment55699401_33971357) with a link to [error code: 1175 during UPDATE in MySQL Workbench](http://stackoverflow.com/questions/11448068/mysql-error-code-1175-during-update-in-mysql-workbench) – Blag Nov 28 '15 at 13:07
  • ok, I removed the downvote, but still your answer isn't helpfull stating it works fine for you. You should try to find out why it isn't working for him – davejal Nov 28 '15 at 13:18
  • @davejal thx; reproducing a bug is the first thing to do : showing the same code working may help to find a mistyping or an environment related bug (like this one, as it's the safemode of workbench in fault and not mysql); but yes, I usually try to be more helpful ;) – Blag Nov 28 '15 at 13:29
1

"Safe mode" is on by default in MySQL workbench. You can change it go to mysqlworkbench at the top left –> preferences–> sql editor –> uncheck the safe mode and then try reconnecting. Or you can just type

SET SQL_SAFE_UPDATES = 0;

This will do the same.

Ashish Chowdhary
  • 61
  • 1
  • 3
0

I don't think it has anything to to with the SAFE UPDATES since you have clearly stated WHERE you wanted to make changes. I had the same issue, but I tried wrapping the column's name in backticks ` and it worked. You can find backticks to the left of number 1 on the keyboard.

One other thing you can try is to SELECT the table and double click on the item you want to UPDATE then apply the changes at the bottom right of the window.

MehRdad G
  • 1
  • 1