How to fix mysql script error for altering table with a computed column [duplicate]

Question

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?

MNB · Accepted Answer · 2024-09-07 14:41:52Z

2

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.

answered Sep 7, 2024 at 14:41

MNB

5476 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to fix mysql script error for altering table with a computed column [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related