Update a column based on other column with postgresql

Question

I have a table with the following columns:

id  integer
sumqty numeric
maxqty  numeric

The columns id, sumqty are updated by other functions regularly. I need to write a function that pass on these two columns and update the maxqty column.

for example:

id, sumqty, maxqty
5    20
5    70
5    45
3    20
1    12
1    2.5

after function runs, desired output will be:

id, sumqty, maxqty
5    20      45
5    10      45
5    45      45
3    20      20
1    12      12
1    2.5     12

I wrote this code:

update A set maxqty= (select MAX(sumqty)  OVER (PARTITION BY id))

but it doesn't always works. Sometimes it doesn't give me the actualy MAX.

What is the problem with my code?

Create a view instead, and you'll never have inconsistent data. (Or use triggers.) It's generally a bad idea to store calculated data (from other columns.) — jarlh
– jarlh, Commented Oct 13, 2015 at 8:04

Lukasz Szozda · Accepted Answer · 2015-10-13 08:37:49Z

6

Use correlated subquery instead:

SqlFiddleDemo

UPDATE mytable t1
SET maxqty= (SELECT MAX(sumqty) 
             FROM mytable t2
             WHERE t1.id = t2.id );

Result:

╔═════╦═════════╦════════╗
║ id  ║ sumqty  ║ maxqty ║
╠═════╬═════════╬════════╣
║  5  ║ 20      ║     45 ║
║  5  ║ 10      ║     45 ║
║  5  ║ 45      ║     45 ║
║  3  ║ 20      ║     20 ║
║  1  ║ 12      ║     12 ║
║  1  ║ 2.5     ║     12 ║
╚═════╩═════════╩════════╝

More efficient way:

WITH cte AS
( 
  SELECT id, MAX(sumqty) AS sumqty
  FROM mytable
  GROUP BY id
)
UPDATE mytable m
SET maxqty = c.sumqty
FROM cte c
WHERE m.id = c.id;

Your original query:

update mytable 
set maxqty= (select MAX(sumqty)  OVER (PARTITION BY id);

Gives:

╔═════╦═════════╦════════╗
║ id  ║ sumqty  ║ maxqty ║
╠═════╬═════════╬════════╣
║  5  ║ 20      ║ 20     ║
║  5  ║ 10      ║ 10     ║
║  5  ║ 45      ║ 45     ║
║  3  ║ 20      ║ 20     ║
║  1  ║ 12      ║ 12     ║
║  1  ║ 2.5     ║ 2.5    ║
╚═════╩═════════╩════════╝

It is because UPDATE works per row. And your subquery has only one row to process not bunch of them.

edited Oct 13, 2015 at 8:37

answered Oct 13, 2015 at 8:10

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

6 Comments

SMW Over a year ago

isn't PARTITION BY id should gives all rows of that id?

Lukasz Szozda Over a year ago

@SMW Think about it as UPDATE mytable SET maxqty = (SELECT MAX(sumqty) OVER(PARTITION BY id) FROM (SELECT id, sumqty, ... ) AS sub);. You have only one row to process.

SMW Over a year ago

yes but the : SELECT MAX(sumqty) OVER(PARTITION BY id should pass on all rows of that id

SMW Over a year ago

OK. is there a way to speed up this querry? when I add it the function becomes very slow. i have over a milion records in the table.

Lukasz Szozda Over a year ago

@SMW added update with CTE. Why do you use function?

|

Vivek S. · Accepted Answer · 2015-10-13 10:01:38Z

0

update mytable 
set maxqty=t.max 
    from (select id,max(sumqty) from mytable group by id) t
where t.id=mytable.id

SQLFIDDLE

edited Oct 13, 2015 at 10:01

answered Oct 13, 2015 at 9:55

Vivek S.

22.3k9 gold badges71 silver badges87 bronze badges

Collectives™ on Stack Overflow

Update a column based on other column with postgresql

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related