I have a table with an identity column defined by:

id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

To begin with, I’ve inserted a few rows manually including the id column:

INSERT INTO things(id, …)
VALUES (…, …), (…, …), (…, …);

I now want to use auto-generated ids for the rest.

I’ve tried:

ALTER TABLE things ALTER COLUMN id RESTART WITH (SELECT max(id) FROM things);

but that doesn’t work.

I’ve even tried:

EXECUTE IMMEDIATE 'ALTER TABLE employees ALTER COLUMN id RESTART WITH ' || (SELECT MAX(id)+1 FROM employees);

I know I can restart with a constant value, but that’s not useful if I can’t predict the final id after the inserts.

Is there a way of dynamically restarting the identity column?

I have a table with an identity column defined by:

id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

To begin with, I’ve inserted a few rows manually including the id column:

INSERT INTO things(id, …)
VALUES (…, …), (…, …), (…, …);

I now want to use auto-generated ids for the rest.

I’ve tried:

ALTER TABLE things ALTER COLUMN id RESTART WITH (SELECT max(id) FROM things);

but that doesn’t work.

I’ve even tried:

EXECUTE IMMEDIATE 'ALTER TABLE employees ALTER COLUMN id RESTART WITH ' || (SELECT MAX(id)+1 FROM employees);

I know I can restart with a constant value, but that’s not useful if I can’t predict the final id after the inserts.

Is there a way of dynamically restarting the identity column?

• sql
• db2
• identity

• What happened when you tried EXECUTE IMMEDIATE – Charlieface Commented Mar 8 at 21:09
• The ALTER TABLE returns an error. The EXECUTE IMMEDIATE doesn’t return an error, so I thought it might work, but it doesn’t work. I still get the conflict error on inserting. – Manngo Commented Mar 8 at 21:20

3 Answers 3

You can define a variable to pull the max(id) in a variable and use the variable in the EXECUTE IMMEDIATE and wrap it in a small anonymous block.

BEGIN
    DECLARE max_id INT;
    SET max_id = (SELECT NVL(MAX(id),0) FROM test); -- incase there is no row, NVL is added to return 0
    EXECUTE IMMEDIATE 'ALTER TABLE test ALTER COLUMN id RESTART WITH ' || (max_id + 1);
END;

Data before running this block

ID  NAME
1   Item 1
2   Item 2
3   Item 3

After running the block and running an insert

INSERT INTO test( name)
VALUES 
( 'Item 4'), 
( 'Item 5');

Outputs

ID  NAME
1   Item 1
2   Item 2
3   Item 3
4   Item 4
5   Item 5

Fiddle Demo

This will help you:

declare @MaxId=(select max(id) from table_name)
DBCC CHECKIDENT ('table_name', RESEED, @MaxId);

You need to first retrieve the max(id) value from your table and then use that value dynamically in an ALTER TABLE statement to restart the identity column. Since ALTER TABLE ... RESTART WITH requires a constant and doesn’t support direct subqueries, you can do the following:

If using MySQL:

SET @max_id = (SELECT MAX(id) FROM things);
SET @sql = CONCAT('ALTER TABLE things AUTO_INCREMENT = ', @max_id + 1);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

MySQL uses AUTO_INCREMENT**,** so ALTER TABLE ... AUTO_INCREMENT is needed.