4

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 :)?

5
  • 1
    Use trigger(s).
    – Akina
    Jan 2, 2023 at 12:38
  • can you make an example? Jan 2, 2023 at 13:41
  • Use [mysql] "create trigger" "after insert on" as searching criteria - on stackoverflow.com primarily.
    – Akina
    Jan 2, 2023 at 14:00
  • Ok I see what you mean. But is not exactly what I am asking and this would work. Because imagine all the records (objects and items) already exists in a old db. Now, we decide to add a new column in objects. On its default value, it would be nice to put a select statement that query itsself. I honestly don't think is possible but I may be wrong or there may be a possible solution. Jan 2, 2023 at 14:26
  • @Akina I added a bit more explanation on why I need this Jan 2, 2023 at 14:28

1 Answer 1

3

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.

2
  • your answer is spot on. Also you said it right about " you only asked about setting their initial value at the time you add the column." in fact there are some reason behind that I did not explain but you somehow saw it by commenting "Now you have another task to keep them in sync going forward" even if I didn't write that. Point +1 for the answer and 1+ Point for hope in humanity. Jan 2, 2023 at 15:21
  • "every task anyone needed" - NULL-less data migration is done in so many companies and by so many people that it should be added, if not as an option for DEFAULT (considering how it's implemented), then as an option for ALTER. This really isn't one's whim...
    – jave.web
    Jul 13, 2023 at 15:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.