m1gin 107

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);


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: