MySQL | Set Column default with value of an existing database that is a SELECT result

Problem

Having an exsiting database, a we need to add a column to a table, this column being of type int, is there a way to set the default initial value a result of a query in the database?

Just to give bit more context of the problem. This 'default' value would be used only initially, when the column is added, then we may alter the new column again givin a normal default (example default 0)

By doing this, you would be able to initialize a value into an existing database, without the needs of a migration (probably trhough a script by looping in the existing objects, counting how many item they have and givin its initial value.

Example:

schema

create table object
(
    object_id       int auto_increment   primary key,
    name            varchar(120)         not null
);
create table item
(
    item_id       varchar(63)         not null,
    object_id int                     not null,
    primary key (object_id, item_id)
);
insert into object (name) VALUES ("hello");
insert into item (item_id, object_id) VALUES
      ("item1", 1),
      ("item2", 1),
      ("item3", 1),
      ("item4", 1);

We can the total amount of item for each object in this way

-- Counting item of object
SELECT COUNT(*) AS total, o.object_id , o.name
    FROM item AS i
    JOIN object o on i.object_id = o.object_id
    GROUP BY o.object_id ORDER BY total DESC LIMIT 50;

total

4

I am looking something like this

ALTER TABLE object ADD COLUMN item_count INT DEFAULT (
    SELECT COUNT(*) AS total
    FROM item AS i
    JOIN object o on i.object_id = o.object_id
    GROUP BY o.object_id ORDER BY total DESC LIMIT 50

    );

If this feature doesn't exists, tell me, wouldn't be a nice idea to have it :)?

No, you can't use a subquery in a DEFAULT clause.

In MySQL 8.0.13, they added a feature to allow a DEFAULT clause to be an expression, but it still can't be a subquery:

https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html#data-type-defaults-explicit

...

• Subqueries, parameters, variables, stored functions, and loadable functions are not permitted.

The way I would do what you describe:

First, add the column normally. It will be NULL on existing rows.

ALTER TABLE object ADD COLUMN item_count INT;

Then UPDATE the table to set the NULLs to the count values you want:

UPDATE object JOIN (
  SELECT object_id, COUNT(*) AS item_count FROM item GROUP BY object_id
) AS i USING (object_id)
SET object.item_count = i.item_count;

Now you have another task to keep them in sync going forward, but you only asked about setting their initial value at the time you add the column.

You also asked:

If this feature doesn't exists, tell me, wouldn't be a nice idea to have it :)?

Of course. It would be nice to have a feature that makes whatever task we are doing today easy to do in a single statement. But if every task anyone needed to do on any day were given its own special-purpose command, then the programming language would need an infinite number of special commands that will only be used once.

StackExchange Database Administrators Q#321633, answer score: 3