How to reset the auto_increment vlaue of a column whenever we truncate table

[rjsv]

We have a table as below. While deleting the records,we will capture the records into this table. To generate the id value we are using ‘auto_increment’ column. We will truncate this table weekly once. How to reset the auto_increment values from the number 1 again whenever we truncated the table.(We don’t want to drop and recreate the table.)

CREATE TABLE IF NOT EXISTS endpoints_audit
(id int auto_increment ,
model varchar(100) not null,
.
.
.
.
.
.’
created_on timestamp default now(),
KEY (model,id) USING CLUSTERED COLUMNSTORE
)

[hanson]

MemSQL doesn’t support a way to reset the auto_increment values. Why don’t you want to drop the table and recreate it, given that you are deleting all the data anyway?

This post talks about how to create your own sequence generator using an auto_increment on a reference table, but of course it is not fully general. It might work for some apps.

Sequence Generator in MEMSQL

We are migrating from Netezza to MEMSQL. However, The sequence generator is integral part of Netezza and it is extensively used in my project. I dont see the corresponding equivalent in MEMSQL. Can someone please suggest How we can go ahead and achieve this in MEMSQL. We tried using AUTO INCREMENT but it does not solve the problem since the number that gets created is arbitrary and unique for a particular batch run only. We need unique values getting incremented by 1 till a certain maximum per…

[ganesh.usi.gk]

Hi Team,

Any update on AUTO_INCREMENT Value to reset features. I could not drop and recreate , i will have permission to truncate/delete the data.so i need to reset the value to 1 whenever i required.

Kindly update me if this is resolved

[hanson]

We don’t support this. We have an open feature request for it. At some point I expect us to add sequences that could be reset and could be used to provide the next value for a table column. That would solve this problem for you. That’s the likely thing we’d do first.

You can adjust your next auto increment value but it must be higher than all the data and all the previously used values:
https://docs.singlestore.com/managed-service/en/reference/sql-reference/data-manipulation-language-dml/aggregator-sync-auto_increment.html
And I see that is not what you want.