Update query not working in mysql workbench

Question

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.

Answer 1

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.

Answer 2

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;

Answer 3

[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. |      *** |

Answer 4

"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.