2024-01-18 m1gin 823
SQLite Tips

2022-07:

Get the next row id when randomly picking some rows.

SELECT id, LEAD(id, 1, 0) OVER (ORDER BY id) AS nextid FROM tblcut ORDER BY RANDOM() LIMIT 5;

I have learned this LEAD function in SQLite.

And now I need to merge these 2 columns in rows so that I can use them in a subquery at ease:

SELECT t1.id FROM tblcut t1,
( SELECT id, LEAD(id, 1, 0) OVER (ORDER BY id) AS nextid FROM tblcut ORDER BY RANDOM() LIMIT 5 ) t2
where t1.id=t2.id OR t1.id=t2.nextid LIMIT 10;



2021-03:

Treat custom values as table

SELECT T.* FROM (VALUES (1, "a"),(2, "b"),(3, "c"),(4, "d"),(5, "e")) as T;

Specify a custom column names:

WITH tblfly(col1, col2) AS
(SELECT * FROM (VALUES (1, "a"),(2, "b"),(3,"c")))
SELECT tblfly.col2 FROM tblfly;


2019-12: Update using row number

WITH dyn AS (SELECT *, ROW_NUMBER() OVER(ORDER BY idorder) AS rn FROM tblplaylist WHERE idgroup=2)
UPDATE tblplaylist SET idorder = (SELECT rn FROM dyn WHERE dyn.id = tblplaylist.id) WHERE idgroup=2;

Update from a dynamically created table

SQLite'ta çok sayıda alan aynı alt sorgudan güncellenebiliyor. Ayrıca güncelleme, dinamik olarak oluşturulan tablodan yapılabilmektedir.

;WITH tblfly AS(
select ID, ArabicWord, (select "a" || Category ||"_"|| (count(*)-1)||"_0.mp3" from tbl_commonwords t2 where t2.Category=t1.Category AND t2.ID<=t1.ID) as num from tbl_commonwords t1 ORDER BY ID
)
update tbl_commonwords set (audio) = (select num from tblfly WHERE tblfly.ID=tbl_commonwords.ID);

2020-04: Multiple results using WITH AS

;WITH tblar AS(
SELECT tatoeba_id FROM tbltatoeba, links WHERE lang='ara' and tatoeba_id=links.id2
),
tbleng AS(
SELECT tatoeba_id FROM tbltatoeba WHERE audio=1 and lang='eng'
)
SELECT * FROM tbltatoeba, links, tblar, tbleng
WHERE links.id1 IN (tbleng.tatoeba_id) and links.id2 IN (tblar.tatoeba_id) AND (tbltatoeba.tatoeba_id=links.id1 OR tbltatoeba.tatoeba_id=links.id2 ) LIMIT 20;


Update Multiple Records with Different Values

update tblplaylist set idorder =
case
when idorder = 6 then (select MAX(idcut) from tblplaylist)
when idorder = 7 then idorder - 1
else idorder
end
where idorder between min(6,7) and max(6,7);

The following is by ChatGPT but not tested (2024-01)

UPDATE tblpoint
SET vote = CASE
WHEN dtup > datetime('now', '-10 seconds') THEN vote + 1
ELSE 0
END;

Insert a record and set value by query:

INSERT INTO tblplaylist(idcut, idgroup, idorder)
VALUES ( 1, 2, (select count(*)+1 from tblplaylist WHERE idgroup=2) );


Import CSV to SQLite db

Using command line...

sqlite3 tatoeba.db -cmd '.separator "\t"' -cmd '.import links.csv links' "SELECT * FROM links LIMIT 11"

Interactive way:

sqlite3 tatoeba.sqlite
sqlite> "create table links(id1 integer, id2 integer)";
sqlite> .separator "\t"
sqlite> .import links.csv links
sqlite> SELECT * FROM links LIMIT 11;


SQLite için, SELECT sorgusunda dinamik olarak artan sayı alanı eklemek

select *, (select count(*) from tbl_commonwords t2 where t2.Category=t1.Category AND t2.ID<=t1.ID) as num from tbl_commonwords t1 where Category=15 ORDER BY ID;

abc, 1
bcd, 2
xyz, 3

Çok sayıda kategori için:

select *, (select "a" || Category ||"_"|| (count(*)-1) from tbl_commonwords t2 where t2.Category=t1.Category AND t2.ID<=t1.ID) as num from tbl_commonwords t1 ORDER BY ID;


2024-09-19 m1gin 0
SQLite Database Notes

I am new in SQLite database. So I am searching for lots of things. So, I thought to crate a post with important SQLite notes.

Get Tables Information and more:

SELECT * from sqlite_master;

Crating Table:

Example for crating a table with primary key, and with unique field:

CREATE TABLE tbl1(id INTEGER NOT NULL PRIMARY KEY, name TEXT, counter INTEGER, url TEXT UNIQUE, daterecord TEXT, rate REAL);

Creating table, with unique value depend on 2 columns:

CREATE TABLE tbl2(id INTEGER, field1 TEXT NOT NULL, field2 TEXT NOT NULL, counter INTEGER, daterecord TEXT, UNIQUE (field1, field2) );

Create a table, with default values for columns:

CREATE TABLE tbl3 (daterecord TEXT DEFAULT CURRENT_TIMESTAMP, counter INTEGER DEFAULT 0, message TEXT DEFAULT 'none');

Create a table from query result:

CREATE TABLE tblnew AS SELECT title, message FROM tbl1 WHERE id<30;

Insert Into Table:

INSERT INTO tbl1(id, name, counter, url, daterecord, rate )
VALUES (5, "a name", 0, "an url", datetime("now"), 0.22);

We can use multiple values at once like:

INSERT INTO tbl2 (firstname, lastname, age)
VALUES ("firstname1", "lastname1", 21), ("firstname2", "lastname2", 22), ("firstname3", "lastname3", 33);

Insert Data From Other Tables

INSERT INTO tbl1 SELECT * FROM table2;

Insert Into Another Database Table:

ATTACH '/media/data/test/db2.sqlite' AS db2;
INSERT OR IGNORE INTO db2.tblTest SELECT * FROM tblTest LIMIT 500;
DETACH db2;

or create a new table by selecting from another database;

ATTACH '/media/data/test/db2.sqlite' AS db2;
CREATE TABLE tblNew AS SELECT * FROM db2.tblTest;
DETACH db2;

This query tested using SqliteCommand.ExecuteNonQuery and worked great!
So, we can copy data from currently connected database to another without need to use sqlite command line utility.

Update From Another Table

Update table from dynamically created table. Multiple fields can be updated from the same sub query.

;WITH tblfly AS(
SELECT ID, firstname lastname FROM tbl1 WHERE ID<300
)
UPDATE tbl2 SET (field1, field2) = (SELECT firstname, lastname FROM tblfly WHERE tblfly.ID=tbl2.ID);

Add New Column to Existing Table

ALTER TABLE tblTest ADD COLUMN newcolumn TEXT;

ALTER TABLE tblword ADD COLUMN counterror INTEGER DEFAULT 0;

Add Primary key or rename column for existing table:

ALTER TABLE commands is limited support in SQLite. So, we can achieve this indirectly.

Let's say the original table was created as follow:

CREATE TABLE table1 (id INTEGER, field1 TEXT, field2 TEXT );

First, rename the original table.

ALTER TABLE table1 RENAME TO table1_temp;

Then create a new table as wanted. Let say we want to rename field2 column to fld2.

CREATE TABLE table1( id INTEGER PRIMARY KEY, field1 TEXT, fld2 TEXT, field3 INTEGER DEFAULT 0);

Now, copy the data from the original table to the new one.

INSERT OR IGNORE INTO table1 (id, field1, fld2) SELECT id, field1, field2 FROM table1_temp;

INSERT OR IGNORE helps if there is duplicate for unique fields. Else, error throwing.
INSERT OR REPLACE also can be used.

And finally, delete the old table:

DROP TABLE table1_temp;

With this approach, the primary keys values stays unchanged, if no conflict. This is very useful.

Note: If you have any indexes or triggers related to the original table, you should be edit them according to new table. For example:

CREATE UNIQUE INDEX idx_table1_1 ON table1 (field1, fld2);

CREATE TRIGGER trg_table1_1 AFTER INSERT ON table1 BEGIN UPDATE table2 SET daterecord = DATE('NOW'), title = new.fld2 WHERE rowid = new.rowid; END;

Get Random Records:

SELECT * FROM tbltest ORDER BY RANDOM() LIMIT 5

Convert Date Formatted Text to SQLite Date Text Format

I convert an Access database to SQLite. The database had a date column. I know SQLite doesn't has date data type. So I stored the date column as text in SQLite.
After conversation I noticed the dates in dd/MM/yyyy HH:mm:ss format.
SQLite date function unsupported this pattern. So I wanted to change the data to yyyy-MM-dd HH:mm:ss format.
After some tries, I succeed to replace the text dates to the new pattern using following query:

UPDATE tblComment
SET DateRecord = substr(DateRecord,7,4) || '-' || substr(DateRecord,4,2) || '-' || substr(DateRecord,1,2) || substr(DateRecord,11,9)
WHERE DateRecord LIKE "__/__/201_ __:__:__"

Date Operation Examples:

Add 6 day to current date:

SELECT date('now','+6 day');

5 hours before now:

SELECT datetime('now','-5 hour');

Get values as local time:

SELECT datetime('now', 'localtime', '-15 minutes');

#from blog.mbirgin.com, archive, sqlite, sqlite db, sqlite notes, sqlite tips, sqlite database, database, sqlite commands, sqlite and .net, programming

Add to:
Contact - About - Help