CODEWITHSUNDEEP
sqlfirebird
ravenx30
ravenx30

Reputation: 416

Firebird SQL command for reindexing a column

Wondering what the SQL command is to re-number a column in firebird, so each row for that column reads:

1, 2, 3, 4, 5, 6 instead of 1, 2, 4, 6, 10, 11..

Upvotes: 0

Views: 716

Answers (2)

Mark Rotteveel
Mark Rotteveel

Reputation: 109236

I have created a simple example to demonstrate this:

CREATE TABLE renumber_example (
    id INTEGER NOT NULL
);

I populated this table with some discontinuous numbers:

INSERT INTO renumber_example (id) VALUES (1);
INSERT INTO renumber_example (id) VALUES (4);
INSERT INTO renumber_example (id) VALUES (5);
INSERT INTO renumber_example (id) VALUES (10);
INSERT INTO renumber_example (id) VALUES (15);

To renumber you can use an EXECUTE BLOCK or a stored procedure:

SET TERM #;
EXECUTE BLOCK
AS
    DECLARE newId INTEGER;
BEGIN
    newId = 1;
    FOR select id from renumber_example order by id AS CURSOR reorder DO
    BEGIN
        UPDATE renumber_example SET id = :newId WHERE CURRENT OF reorder;
        newId = newId + 1;
    END
END#
SET TERM ;#

Note: SET TERM is not actually part of the Firebird SQL language, but it is part of most SQL clients for Firebird (e.g. isql, FlameRobin). If you execute this directly from your own application using a client library (eg fbclient.dll, Jaybird, Firebird .net provider etc), then you must leave out the SET TERM statements.

With Firebird 3 and higher you can also do this with a single MERGE statement that uses the ROW_NUMBER() window function:

MERGE INTO renumber_example as target
    USING (
        SELECT id, ROW_NUMBER() OVER(ORDER BY id) as newId 
        FROM renumber_example
    ) AS source
    ON target.id = source.id
    WHEN MATCHED THEN
        UPDATE SET target.id = source.newId;

However, as I mentioned earlier in my comments: please consider carefully why you want to do this and if you really need to. If the numbers are meaningless then you shouldn't care about the gaps, and if they do have meaning, then chances are those values also have meaning outside your system in which case renumbering 'breaks' the external truth. Also, if these values are primary or unique keys with foreign keys then you need to ensure that all foreign keys are ON UPDATE CASCADE.

Upvotes: 3

Edin Omeragic
Edin Omeragic

Reputation: 1968

If you mean to update column position you can use this:

ALTER TABLE tablename ALTER [COLUMN] colname POSITION <newpos>
<newpos>  ::=  an integer between 1 and the number of columns

http://www.firebirdsql.org/refdocs/langrefupd20-alter-table.html

Upvotes: 1

Related Questions