I need to add a new column NOT NULL whose value should be proportional to other field. Now the proportion is the same, but in the future could be diferent
By now my current script
ALTER TABLE myTable ADD COLUMN (basePrice FLOAT(10,2) NULL);
UPDATE myTable set basePrice = 0,9 * price;
ALTER TABLE myTable MODIFY COLUMN (basePrice FLOAT(10,2) NOT NULL);
but I would like to know if there is a way to do this in a single query in order to reduce the required time and avoid locks.
Several things to note:
This gets rid of one of the ALTERs:
ALTER TABLE myTable ADD COLUMN basePrice FLOAT(10,2) NOT NULL DEFAULT '0';
UPDATE myTable set basePrice = 0,9 * price;
MySQL 8.0 can add a column essentially instantly. (There would still need to be the UPDATE.
pt-online-schema-change can do the task with virtually zero lock time.
The UPDATE can (should) be done in chunks of, say, 1000 rows. Each chunk would be its own transaction. This minimizes locks during the update. More discussion here .
(And I strongly agree with Strawberry about using DECIMAL(8,2) (or whatever size) for money.)
MySQL 5.7 can have a "generated" column, but I suspect it will not buy you anything since you expect to change the formula later.