PostgreSQL Multiple Update

Question

I have a situation... i have these columns - table X

 Product   Number  LastEditDate    GUID
 A         0       14.12.16        A1
 A         0       10.12.16        A2
 B         0       10.12.16        A3
 B         0       13.12.16        A4
 C         0       13.12.16        A5
 C         0       14.12.16        A6

Now, i need to put Number 1 to all products where the Date is the Max. I was using this query:

update X set Number=0;
update X set Number=1 where LastEditDate in (select Max(LastEditDate) from X Group by Product);

So this will give me something like this:

Product    Number    LastEditDate
A          1         14.12.16
A          0         10.12.16
B          0         10.12.16
B          1         13.12.16
C          0         13.12.16
C          1         14.12.16

But, with my query, i have: 1;0;0;1;1;1; because 13.12.16 will be in that "select Max(LastEditDate) from X). How to make like that, to have results: 1;0;0;1;0;1;???

Please help!

Sorry, there should be a "Group by Product"...i missed it... — Andrei
– Andrei, Commented Dec 14, 2016 at 14:12

klin · Accepted Answer · 2016-12-14 15:24:06Z

1

You should look for the pair (product, lasteditdate):

update x 
set number = 1 
where (product, lasteditdate) in (
    select product, max(lasteditdate) 
    from x 
    group by product);

answered Dec 14, 2016 at 15:24

klin

123k15 gold badges240 silver badges262 bronze badges

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

1 Comment

Andrei Over a year ago

Thank you, i think this should work. I just don't understand why i didn't this before? :) Thank you

McNets · Accepted Answer · 2016-12-14 15:24:23Z

0

You can check it here: http://rextester.com/YVLK16496

with t1 as (
    select Product, Max(LastEditDate) as MaxDate from t group by Product
)
update t
set Number=1
from t1
where t.Product = t1.Product and t.LastEditDate = t1.MaxDate;

answered Dec 14, 2016 at 15:24

McNets

10.9k3 gold badges39 silver badges70 bronze badges

1 Comment

Andrei Over a year ago

This also could work for me but i will try the "klin" answer. Thank you

Collectives™ on Stack Overflow

PostgreSQL Multiple Update

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related