i have the following table change script, that adds a computer column where it combines the country_code and mobile which are defined as big integers (don’t ask why)
alter table schema.clients
add phone varchar(50) null;
update schema.clients
set phone = Concat(trim(cast(country_code as char(10))), trim(cast(mobile as char(20))) )
where country_code is not null and mobile is not null;
create unique index idx_phone on schema.clients(id, phone);
Whenever I run this script thru mySQl Workbench i get the following 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 Editor and
reconnect.
Whats wrong with my script and how can I correct it?
0
sounds like you have “safe-updates” turned on. This feature is intended to protect new users from themselves, and when turned on all updates (or deletes) that do not include unique or primary keys in the where clause will fail. This stops people accidentally leaving out the where clause entirely and accidentally issuing an unrestricted update or delete, it is intended to be useful but I’ve never found it helpful myself.
The fix is to disable this option in your SQL client.
See https://dev.mysql.com/doc/refman/8.4/en/mysql-tips.html#safe-updates for more information.
Edited to add – you can change this option MySQL Workbench (the client) preferences. You’ll need to edit your connection and then close and re-open it to allow the new setting to take effect.