-- Leo's gemini proxy
-- Connecting to freeshell.de:1965...
-- Connected
-- Sending request
-- Meta line: 20 text/gemini;lang=en-GB
I knew these things in the sense that I'd seen them before, but I had to re-learn them today.
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.
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.
-- Response ended
-- Page fetched on Fri May 3 21:10:28 2024