SQL Trickz

My favorite database engine is sqlite. Mostly because it's small, fast, reliable, and super easy to use. Here are some things I commonly use, that I really just want to copy / paste from this webpage.

Recursive CTE's

with linear as
(
    select 1 as x
    union all
    select x + 1 from linear where x + 1 < 10
)
select * from linear;

Update / Delete Using a CTE

with newvals(id, val) as (...)
update mytable
set val = (select val from newvals where id = mytable.id);

with ids as (...)
delete from mytable
where id in ids;