0

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.

Community
  • 1
  • 1
giordano
  • 2,954
  • 7
  • 35
  • 57
  • In which order should the rows be counted? – CL. Jan 25 '16 at 22:02
  • Order plays no rule. The idea is to add an identification number. – giordano Jan 26 '16 at 07:57
  • So you don't actually need a counter, but just a unique value? – CL. Jan 26 '16 at 08:50
  • Yes! I asked for the counter since a possible solution for this simple example may be more flexible if for example I want to count within groups (e.g. [here](http://stackoverflow.com/questions/20055644/mysql-counter-within-group)). – giordano Jan 26 '16 at 09:09

1 Answers1

1

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
              );
CL.
  • 173,858
  • 17
  • 217
  • 259