How to add bulk values in column using DB Browser SQLite

Question

I have a table that contain two columns. One column "rewords" contain 12186 words. and the other column named "ID" is the column that I newly created so it is empty. I want to add numbers against the rewords column. numbers from 1, 2, 3, to 12186. i tried to use primary key not null operation but it disturbs the order and its sequence becomes unpredictable like 1,2,6,10,7,8,19.... If I manually type 1,2,3 in the fields, then it will be too time-consuming and effortful. Following is the screenshot of what I desire. I want that I could have typing from 1 to 12186 in the fields of ID column.

I also tried the following function in the query tab, but it doesn't work and gives error that "near "for": syntax error: for"

for(int i = 1; i<=12186; i++){
    UPDATE tbl SET ID = i WHERE index = i;
}

ScaisEdge · Accepted Answer · 2018-10-23T18:53:39.073

1

you don't need the loop you can do this with a single query

UPDATE tbl 
SET ID =  index 
WHERE index  <= 12186;

otherwise if you have not a column index .. you need an autoincrement value in you id column .. you should

You can do it with SQLite Expert Personal 4:

1) Select the table and then go to "Design" tab > "Columns" tab.

2) Click select the ID column name, and type INTEGER and Not Null > Ok.

3) Go to "Primary Key" tab in "Desgin tab". 
      Click "Add" and select the column ID. Check the "Autoincrement" box.

4) Click "Apply" on the right bottom part of the window.

edited Oct 23 '18 at 18:53

answered Oct 23 '18 at 18:42

ScaisEdge

131,976
10
91
107

near "index": syntax error: UPDATE tbl SET ID = index – Surraya Marvi Oct 23 '18 at 18:44
have you a column named index in your table ? ..anyway check better for the code .. i don't see sintax error in my code – ScaisEdge Oct 23 '18 at 18:47
no. but I think index is the very first unnamed column counting as you can see in the screenshot – Surraya Marvi Oct 23 '18 at 18:49
answer updated .. seems you need an autoincrement column .. (you could also create the table with the auto increment) – ScaisEdge Oct 23 '18 at 18:54
I tried primary key option, but it doesn't follow the order 1,2,3,4,5.... It generates like this: 1, 2, 63, 64, 65, etc... – Surraya Marvi Oct 23 '18 at 18:58
seems strange .. the autoincrement .. is by 1 normally .. check selecting order by id – ScaisEdge Oct 23 '18 at 19:00

How to add bulk values in column using DB Browser SQLite

1 Answers1