August 10th, 2012, 05:56 AM
Firebird 2.5 - add unique ID to each row in a table from stored procedure
I have a table which doesn't have an unique ID. I want to make a stored procedure which is adding to each row the number of the row as ID, but I don't know how to get the current row number. This is what I have done until now
CREATE OR ALTER PROCEDURE INSERTID_MYTABLE
declare variable rnaml_count integer;
/* Procedure Text */
Cnt = 1;
for select count(*) from MYTABLE r into:rnaml_count do
while (cnt <= rnaml_count) do
update MYTABLE set id=:cnt
where :cnt = /*how should I get the rownumber here from select??*/
Cnt = Cnt + 1;
Thanks in advance!
August 10th, 2012, 06:32 AM
I got an answer on StackOverflow - question Firebird 2.5 - add unique ID to each row in a table from stored procedure :
Add new nullable column (let's call it ID).
Create a generator/sequence (let's call it GEN_ID).
Create a before update/insert trigger that fetches new value from sequence whenever the NEW.ID is null. Example.
Do update table set ID = ID. (This will populate the keys.)
Change the ID column to not null.
A bonus. The trigger can be left there, because it will generate the value in new inserted rows.
Can I accomplish this from an SP ?
August 13th, 2012, 10:22 AM
If you follow using a generator, a simple:
UPDATE mytable SET id=gen_id(yourgenerator, 1)
will do. No need for a stored procedure.