SQL notes to self

I knew these things in the sense that I'd seen them before, but I had to re-learn them today.

How do you get a value from the previous row?

With "lag"

Given...

select * from example;
id |name
---+-----
  1|One
  2|Two
  3|Three

...you can do this:

select
    id,
    lag(name) over (order by id) as prev_name
from
    example;
id |prev_name
---+---------
  1|(null)
  2|One
  3|Two

There's also "lead" which works in the opposite direction.

How do you update a table with the result of a query?

With "merge"

There are other ways to do this, but this one's in the standard.

merge into
    sometable t
using
    (
        -- some query goes here
    )
    q on q.id = t.id  -- join query to table
when matched then
    update
        set
            t.foo = q.new_foo,
            t.bar = q.new_bar
;

Pleasantly, the query chooses which rows to update, and what values to use.

Also works with "when not matched" and "delete" or "insert" at the end.

=> back to gemlog