how to update missing records in sequence

Question

i have missing records in a sequence and my current output looks like this

|       1882 |   25548860 |         4 | 30                  | null      |       null |
|       1882 |   25548861 |         4 | 30                  | null      |       null |
|       1882 |   25548882 |         4 | 30                  | null      |       null |
|       1882 |   25548883 |         4 | 30                  | null      |       null |
|       1882 |   25548884 |         4 | 30                  | null      |       null |
|       1882 |   25548885 |         4 | 30                  | null      |       null |
                         missing records in between until 2122
|       2122 |   25548860 |         4 | 30                  | null      |       null |
|       2122 |   25548861 |         4 | 30                  | null      |       null |
|       2122 |   25548882 |         4 | 30                  | null      |       null |
|       2122 |   25548883 |         4 | 30                  | null      |       null |
|       2122 |   25548884 |         4 | 30                  | null      |       null |
|       2122 |   25548885 |         4 | 30                  | null      |       null |

I want my output to be in below format. Suggest me a sql query that will update the records in monetdb between 1883 to 2121.

|       1882 |   25548860 |         4 | 30                  | null      |       null |
|       1882 |   25548861 |         4 | 30                  | null      |       null |
|       1882 |   25548882 |         4 | 30                  | null      |       null |
|       1882 |   25548883 |         4 | 30                  | null      |       null |
|       1882 |   25548884 |         4 | 30                  | null      |       null |
|       1882 |   25548885 |         4 | 30                  | null      |       null |

|       1883 |   25548860 |         4 | 30                  | null      |       null |
|       1883 |   25548861 |         4 | 30                  | null      |       null |
|       1883 |   25548882 |         4 | 30                  | null      |       null |
|       1883 |   25548883 |         4 | 30                  | null      |       null |
|       1883 |   25548884 |         4 | 30                  | null      |       null |
|       1883 |   25548885 |         4 | 30                  | null      |       null |
     ........   ..........
     ........   ..........
|       2122 |   25548860 |         4 | 30                  | null      |       null |
|       2122 |   25548861 |         4 | 30                  | null      |       null |
|       2122 |   25548882 |         4 | 30                  | null      |       null |
|       2122 |   25548883 |         4 | 30                  | null      |       null |
|       2122 |   25548884 |         4 | 30                  | null      |       null |
|       2122 |   25548885 |         4 | 30                  | null      |       null |

Answer 1

If you know in advance the range of missing ids, you can use generate_series(). Assuming that your table is called mytable and has columns (id, col1, col2, col3, col4, col5), you can duplicate the records that have id 1882 to fill the gap with the following query:

insert into mytable (id, co11, col2, col3, col4, col5)
select value, col1, col2, col3, col4, col5
from sys.generate_series(1883, 2121, 1)
cross join mytable t
where t.id = 1882

Answer 2

Assume the schema of your table is something like:

create table mytable(id int, col1 int, col2 int, col3 int, col4 int, col5 int);

You can fill in the "missing records" with:

insert into mytable
select *, 4, 30, null, null 
from sys.generate_series(1884, 2121, 1),
    (select distinct col1 from mytable where id = 1883) as tmp;

However, the new records will be appended to the existing records, so if you want to have them returned in the order you showed above, you need an additional order by:

select * from mytable order by id, col1;