postgresql update newest rows

Question

I have some problems with update query. My task is to change status in news rows of my data example:

id     product_id  import_id     modified_at           status
30470     30470        5     "2014-12-08 14:46:22"     "NEW"
96091     30470        7     "2014-12-18 06:11:10"     "CURRENT"
161203    30470        9     "2014-12-29 12:05:35"     "CURRENT"
253973    30470        11    "2014-12-31 06:11:08"     "CURRENT"
317167    30470        12    "2014-12-31 06:12:18"     "CURRENT"
362304    30470        14    "2015-01-01 06:11:09"     "CURRENT"

So in my data example I have to update row with id 362304 and set status "NEW". Of course I will have to do it for all of the products, but first I want to try with this one example.

But why? Never store status values like that, you'll end up with data inconsistency sooner or later... use a view instead that returns the status at select time! — jarlh
– jarlh, Commented Feb 19, 2015 at 14:50
Why do you want to store and change the status? If it's just the MAX(modified_at), you can get the status realtime without any problems. — Frank Heikens
– Frank Heikens, Commented Feb 19, 2015 at 14:54
I work with system that is written in suche way. We have some bugs and because of it we have to update all products. I do not understand comment of jarlh, could you give me some example? — Purzynski
– Purzynski, Commented Feb 19, 2015 at 14:58

mkutyba · Accepted Answer · 2015-03-05 07:49:03Z

2

jarlh's idea with view is good, so I gave it a try. I created a view with query like below.

SELECT
    s.id
    ,s.product_id
    ,s.import_id
    ,s.modified_at
    ,case when
            (
            SELECT max(tmp.id)
            FROM stack28609281 tmp
            WHERE tmp.product_id = s.product_id
            ) = s.id
        then "NEW"
        else "CURRENT"
        end as status
FROM stack28609281 s

After creating this view you can change the query in application to use your view instead of the table.

edited Mar 5, 2015 at 7:49

answered Mar 5, 2015 at 7:37

mkutyba

1,4271 gold badge10 silver badges26 bronze badges

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

Comments

Purzynski · Accepted Answer · 2015-02-19 14:46:37Z

1

I solved my problem so I will share it with you. Maybe there is a better way to do it. If you have some idea please share it.

UPDATE 
    product_history 
SET 
    status = 'NEW'
FROM 
(
    SELECT 
        ph2.product_id, max(ph2.id) as id 
    FROM 
        product_history ph2
    WHERE 
        ph2.product_id = 30470
    GROUP BY ph2.product_id
) AS ph_temp
WHERE 
    product_history.id = ph_temp.id

answered Feb 19, 2015 at 14:46

Purzynski

1,2572 gold badges15 silver badges37 bronze badges

Collectives™ on Stack Overflow

postgresql update newest rows

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related