m1gin 498

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;



Add to: