Thread: Update Current Row Based on Prior Row

Philip Brooks <philswatch@yahoo.com> writes:
> 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.

Really?  Seems to work for me.

            regards, tom lane

Philip Brooks <philswatch@yahoo.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°