Increment Counter or Insert Row in One Statement, in Sqlite

Increment counter or insert row in one statement, in SQLite

I got this answer from Igor Tandetnik on sqlite-users:

INSERT OR REPLACE INTO observations
VALUES (:src, :dest, :verb,
  COALESCE(
    (SELECT occurrences FROM observations
       WHERE src=:src AND dest=:dest AND verb=:verb),
    0) + 1);

It's slightly but consistently faster than dan04's approach.

Sqlite Insert or Replace query with increment of an integer field

How about changing the query to use ifnull or coalesce.

insert or replace into poet (_id,Name, count) values (
   (select _id from poet where Name = "SearchName"),
   "SearchName",
    ifnull((select count from poet where Name = "SearchName"), 0) + 1)

How to insert a query into SQLite with an autoincrementing value for each row

rowid is already there. You can just do:

CREATE TABLE queryset_cache AS 
    SELECT t.*
    FROM mytable t
    ORDER BY product;

You will see it if you do:

SELECT rowid, t.*
FROM queryset_cache;

Here is a db<>fiddle

SQLite auto increment on INSERT and UPDATE

Make a TABLE that hold nothing but the value of the counter call this each time you add a new record or update and increase it by a value of 1 and write that value back to the TABLE with an update I am not sure what you do when you delete a record but the same thing as above and just decrease the counter value by 1

If you do not min could you tell us why a record need to have an ID and a psudo ID it does not matter what the true ID is OR am I missing what your are doing I am talking about Sqlite only

Problem using SQLite Trigger to increment a value

In the trigger, you can use [pseudo-tables new or old] to refer to the row that is being modified:

UPDATE department
   SET employees = employees + 1
WHERE department.department_id = new.department_id;

Here is a demo on DB Fiddle.

SQLITE - Update table with auto-incremented id by group

You may use DENSE_RANK function as the following:

With CTE As
(
Select category, projectName, fileName, fileLine, fdate, groupid,
       DENSE_RANK() Over (Order By projectName, fileName, fileLine) As grp
From my_data
)
Update my_data Set groupid= CTE.grp
From CTE
Where CTE.projectName = my_data.projectName And
      CTE.fileName= my_data.fileName And
      CTE.fileLine = my_data.fileLine

See a demo from db<>fiddle.

I think that you don't need to store this value, since you can simply get it with a select statement:

Select category, projectName, fileName, fileLine, fdate, 
       DENSE_RANK() Over (Order By projectName, fileName, fileLine) As groupid
From my_data

---

Related Topics