O'Reilly logo

Joe Celko's Thinking in Sets: Auxiliary, Temporal, and Virtual Tables in SQL by Joe Celko

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

15.1. Procedural Solutions

The usual replacement for renumbering is to move the data from the current table to a temporary working table with an auto-increment on it. This will close up gaps, and if you do it with a cursor, you can pick the sort order. In SQL Server dialect, it usually looked like this:

CREATE TABLE #temptable
(row_num INTEGER IDENTITY (1, 1) PRIMARY KEY NOT NULL,
   cola INTEGER NOT NULL,
   colb INTEGER NOT NULL,
..);

-- insert the transactions
INSERT INTO #temptable (cola, colb, ..)
SELECT cola, colb, ..
  FROM Mytable -- same structure as #temptable
 ORDER BY cola;

The # prefix creates a local temporary table that disappears at the end of the session. IDENTITY is the dialect syntax for their auto-increment. And, yes, this eats up ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required