How to Renumber Primary Index

How to renumber primary index

I agree other methods will work but I'm just giving a different idea. This will do without any temp table creation requirements::

SET @i=0;
UPDATE table_name SET column_name=(@i:=@i+1);

How to renumber primary index of a table using spring annotation?

Your queries set SET @i=0; UPDATE Employee e SET e.id=(@i:=@i+1) is correct. Simply alter it slightly - move variable initialization into UPDATE: UPDATE Employee e, (SELECT @i:=0) var SET e.new_column =(@i:=@i+1);

your query working fine in MySQL workbench. the problem is that the @Query() annotation always search for a parameter after :

If so you may fill new column with enumeration by

UPDATE Employee t0
  JOIN ( SELECT t1.id, COUNT(t2.id) cnt
         FROM Employee t1
         JOIN Employee t2 ON t1.id >= t2.id
         GROUP BY t1.id ) t3 ON t0.id = t3.id
SET t0.new_column = t3.cnt;

modelling fiddle

Reorder / reset auto increment primary key

You could drop the primary key column and re-create it. All the ids should then be reassigned in order.

However this is probably a bad idea in most situations. If you have other tables that have foreign keys to this table then it will definitely not work.

How to reset the primary key of a table?

alter table foo AUTO_INCREMENT = 1

Query to Re-index Primary Key of MySQL Database

There is even a simple way to accomplish the result by writing this query

SET @newid=0;
UPDATE tablename SET primary_key_id=(@newid:=@newid+1) ORDER BY primary_key_id;

This query will reindex the primary key starts from 1

How to recalculate primary index?

set @pk:=0;

update 
  your_table
  set pk=@pk:=@pk+1
  order by pk;       <-- order by original pk

In my opinion, having a big surrogate key is fine. You probably unlikely to use up all the max allowed integer. Consider you can double it up using unsigned.

If primary key is clustered index in a table then the other columns are eg any unique column is a table is non clustered?

Yes, a given table can only have one clustered index, and in InnoDB, it is always the primary key. All other indexes on that table are non-clustered.

The clustered index also stores all the columns of the table, including non-indexed columns, so in your example the FirstName, LastName, Address, and City are stored as fields in the leaf nodes of the clustered index.

A secondary index stores only the indexed columns, and the primary key of the table. So a search for all the columns of a table by a secondary index would execute two index searches: first to find the value you are searching in the secondary index, which would yield the primary key values in the rows where the value you were searching for occurs. Second, the primary key values would be used to search the clustered index, to get the other non-indexed columns. The InnoDB storage engine does both steps automatically, and delivers all the columns to you.

Reset auto increment counter in postgres

If you created the table product with an id column, then the sequence is not simply called product, but rather product_id_seq (that is, ${table}_${column}_seq).

This is the ALTER SEQUENCE command you need:

ALTER SEQUENCE product_id_seq RESTART WITH 1453

You can see the sequences in your database using the \ds command in psql. If you do \d product and look at the default constraint for your column, the nextval(...) call will specify the sequence name too.

---

Related Topics