Skip site navigation (1) Skip section navigation (2)

Re: Update Current Row Based on Prior Row

From: Andreas Kretschmer <akretschmer(at)spamfence(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Update Current Row Based on Prior Row
Date: 2012-06-03 07:37:57
Message-ID: 20120603073757.GA8740@tux (view raw or flat)
Thread:
Lists: pgsql-novice
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°

In response to

pgsql-novice by date

Next:From: Van Orden, BradDate: 2012-06-04 10:51:27
Subject: Re: database in different location owned by different user
Previous:From: Tom LaneDate: 2012-06-02 21:17:11
Subject: Re: Update Current Row Based on Prior Row

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group