Re: Fwd: Tricky join and update with same table

On 26 July 2013 17:05, Luca Ferrari <fluca1978(at)infinito(dot)it> wrote:

> On Fri, Jul 26, 2013 at 4:36 PM, James David Smith
> <james(dot)david(dot)smith(at)gmail(dot)com> wrote:
> > Luca sent me an email a short while ago with some code to try however,
> and
> > I'm doing that at the moment. Though it's been running for about 30
> minutes
> > now and isn't done. Though there are 230,000 rows, so perhaps that isn't
> > that surprising after all. Here it is for posterity:
> >
>
> Ops...I was supposed to hit the "reply all" button!
> Supposing it is working for you, you can at least split the update
> into chunks insering a condition on the main query to update only rows
> within a certain date range (let's say the most recent ones). That
> will tell you if the query is working properly.
> That is als the reason why I suggested using a trigger for further
> inserts: I was suppsoing you had a lot of data and therefore doing an
> update of chunks when the data is inserted does not make you have to
> run a very long query.
>
> Luca
>

Hi Luca/Michael,

I'm afraid that your query didn't work. It updated too many rows. I've
managed to get the work done that I needed to do using the below queries
i.e. using a temporary table. However if anyone can figure out how to roll
these into one big query that'd be very useful for me long term please.

SELECT
point_geom,
point_time,
a.spid
INTO
first_moment
FROM
hlhs_day a
JOIN (
SELECT
hlhs_day.spid,
min(hlhs_day.point_time) as min_point_time
FROM
hlhs_day
JOIN
hlhs_day as other1
ON
hlhs_day.point_time = other1.point_time
WHERE
hlhs_day.point_geom IS NOT NULL
GROUP B
hlhs_day.spid
) c
ON
a.point_time = c.min_point_time
AND
a.spid = c.spid
ORDER BY
spid,
point_time;

UPDATE
hlhs_day
SET
point_geom = first_moment.point_geom
FROM
first_moment
WHERE
(hlhs_day.point_time < first_moment.point_time
AND
hlhs_day.spid = first_moment.spid
AND
hlhs_day.point_geom IS NULL);

Thanks

James