CODEWITHSUNDEEP
sqlitecounter
giordano
giordano

Reputation: 3162

sqlite: How to add a counter to an existing table

This is an sqlite specific question. I have a table x0 and would like to add a counter:

ALTER TABLE x0 ADD counter INTEGER;
UPDATE x0 SET counter = ?

With ? something like: @counter := @counter +1.

Any idea?

Edit 1:
I found an easy solution but not very flexible:

UPDATE x0 SET counter = ROWID;

But this is not very flexible. For example, if I want to count within groups like in this example with MySQL: count within groups.

Upvotes: 0

Views: 795

Answers (1)

CL.
CL.

Reputation: 180192

Most tables already have a unique identifier, in the form of the rowid, so you could just replace ? with rowid.

If you want to have consecutive values, or restarting counting within some group, you have to manually count how many other rows are before each row:

UPDATE x0
SET counter = (SELECT COUNT(*)
               FROM x0 AS x02
               WHERE x02.rowid <= x0.rowid
               -- AND x02.group_value = x0.group_value
              );

Upvotes: 1

Related Questions