CODEWITHSUNDEEP
sqlpostgresql
Bijan
Bijan

Reputation: 6772

How to update multiple rows with subquery?

I am trying to save the results of a subquery into a table.

select (b.close - a.close) AS difference from day_ohlcs AS a inner join day_ohlcs AS b ON a.id + 1 = b.id

The speed of the subquery is fast, but when I try to put it in an update statement it takes forever, so I know I'm doing something wrong.

UPDATE day_ohlcs
SET price_change = t.difference
FROM (select (b.close - a.close) AS difference
    FROM day_ohlcs AS a 
    inner join day_ohlcs AS b 
    ON a.id + 1 = b.id) 
AS t
WHERE day_ohlcs.instrument_id = 1;

What am I not understanding? Thanks in advance.

Upvotes: 0

Views: 110

Answers (2)

Denis de Bernardy
Denis de Bernardy

Reputation: 78413

The semantics are very different between the two queries. Note that day_ohlcs appears twice vs three times -- the latter does a great deal too many calculations, essentially calculating the difference for the entire table.

You can remove the unneeded behavior like so:

UPDATE day_ohlcs as a
SET price_change = b.close - a.close
FROM day_ohlcs AS b
WHERE a.id + 1 = b.id and a.instrument_id = 1;

http://www.postgresql.org/docs/current/static/sql-update.html

Upvotes: 1

roman
roman

Reputation: 117345

I think that's because you haven't specify column of day_ohlcs to join on, like:

UPDATE day_ohlcs
SET price_change = t.difference
FROM (select a.id, (b.close - a.close) AS difference
    FROM day_ohlcs AS a 
    inner join day_ohlcs AS b 
    ON a.id + 1 = b.id) 
AS t
WHERE day_ohlcs.instrument_id = 1 and day_ohlcs = t.id

Actually looks like you can do this without subquery

update day_ohlcs as a set
   price_change = b.close - a.close
from day_ohlcs as b 
where b.id = a.id + 1 and a.instrument_id = 1

Upvotes: 1

Related Questions