Re: Update Current Row Based on Prior Row

Philip Brooks <philswatch(at)yahoo(dot)com> wrote:

> I had what I thought was a simple update query.. and it has stumped me.
> I have a table ordered by a SERIAL named ROWID.
> In that table I have a column named TITLE.
> Where TITLE is null, I want to set it equal to the previous row's TITLE.
>
> In SQLite, I did it with this:
>
> UPDATE bf
> SET title = (
> SELECT title
> FROM bf AS prev
> WHERE title IS NOT NULL AND prev.rowid < bf.rowid
> ORDER BY prev.rowid DESC
> LIMIT 1
> )
> WHERE title IS NULL
>
> The update is not porting to Postgresql.

Works for me:

test=# select * from bf order by rowid;
rowid | title
-------+-------
1 | row1
2 |
3 | row3
4 | row4
5 |
6 |
7 | row7
8 | row8
(8 rows)

Time: 0,220 ms
test=*# update bf set title = (select title from bf as prev where title
is not null and prev.rowid < bf.rowid order by prev.rowid desc limit 1)
where title is null;
UPDATE 3
Time: 0,435 ms
test=*# select * from bf order by rowid;
rowid | title
-------+-------
1 | row1
2 | row1
3 | row3
4 | row4
5 | row4
6 | row4
7 | row7
8 | row8
(8 rows)

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°