Postgres Update column with another rows data

Question

Ok I have two tables

measures
attr_id, period, net_orders, ref_1 (key = attr_id,period)

and

policy
attr_id, lead_time

What I need to do is grab the 'net_orders' from measure at period (which is a date), Add the 'lead_time' and update the measure table 'ref_1' where period = period+lead

I currently have the select that gets me the data I need but I keep losing myself in my head when trying to figure out the where clauses.

SELECT 
  m.attr_id, 
  m.period, 
  m.net_orders, 
  p.lead_time,
  DATE(m.period) + CAST(p.lead_time as INTEGER) as updateperiod
FROM 
  measures m 
  INNER JOIN policy p ON p.attr_id = m.attr_id

I am stuck with some of the following query - aka incomplete

UPDATE
  measures m
SET
  ref_1 = (SELECT m1.net_orders FROM measures m1 
           WHERE m1.attr_id = m.attr_id AND m1.period = m.period)
WHERE
  attr_id = (SELECT m3.attr_id 
             FROM measures m3 WHERE m3.attr_id = m.attr_id   
             AND m3.period = m.period)
  AND m.period = (SELECT DATE(m2.period) + CAST(p2.lead_time AS INTEGER) 
             FROM measures m2 INNER JOIN policy p2 ON p2.attr_id = m2.attr_id
             WHERE m2.attr_id = m.attr_id AND m2.period = m.period)

EDIT

update measures m
set reference_1 = s.net_orders
from (
    select
        m.attribute_id, period, net_orders,
        DATE(period) + CAST(lead_time as integer) as periodlevel
    from
        measures m
        inner join policies p on p.attribute_id = m.attribute_id
) s
where
    m.attribute_id = s.attribute_id
    and m.period = s.periodlevel

This is the query that has ended up working. I was getting errors with first answer but looks like it is working now!

The first part of the update query works. AKA if i drop the where, it will set the current net_orders to itself. Adding the WHERE clause returns 0 rows updated — Scottzozer
– Scottzozer, Commented May 9, 2013 at 15:25

Clodoaldo Neto · Accepted Answer · 2013-05-09 16:20:27Z

1

update measures m
set ref_1 = s.net_orders
from (
    select
        m.attr_id, period, net_orders,
        period::date + lead_time::int period
    from
        measures m
        inner join
        policy using(attr_id)
) s
where
    s.attr_id = m.attr_id
    and s.period = m.period::date

answered May 9, 2013 at 16:20

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Scottzozer Over a year ago

what is the reason you placed the s.attr = m.attr instead of the other way around?

Clodoaldo Neto Over a year ago

@Scottzozer You mean this s.attr_id = m.attr_id? The order should not make a difference. Are you sure?

Scottzozer Over a year ago

yep I tried it both ways and it only copies over with m.attr_id = s.attr_id

Collectives™ on Stack Overflow

Postgres Update column with another rows data

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related