1

(Translated from Portuguese with Google Translate)

I created a table and made an update that should update column B equally to the percentage that C represents in relation to the maximum value of column C.

create table z(
a serial primary key ,
b numeric(7,2),
c numeric(7,2)
);

insert into z values(default, 0,20);
insert into z values(default, 0,10);
insert into z values(default, 0,40);
insert into z values(default, 0,80);

But it shows an error while executing.

update z set b = c / max(c) * 100;

aggregate functions are not allowed in UPDATE

The expected result is:

a   b      c
1   25     20
2   12.5   10
3   50     40
4   100    80
  • Why do you want to store this value? It might change later requiring an update to all rows. – Colin 't Hart May 12 at 12:36
0

You just need a select within your update:

update z 
set b = c / (SELECT max(c) FROM z) * 100;

Check it at db<>fiddle here

max(c) without more context is not part of the SQL standard. However, you can always use a SELECT returning a single row with a single column (of the appropriate type) where a value is expected.

That's what you'll get:

SELECT
    *
FROM
    z;

  a |      b |     c
 -: | -----: | ----:
  1 |  25.00 | 20.00
  2 |  12.50 | 10.00
  3 |  50.00 | 40.00
  4 | 100.00 | 80.00

And this works not only in PostgreSQL. See SQL Server, or MariaDB, or Oracle, or SQLite, or DB2

  • 2
    The real question is why store this derived value. – Colin 't Hart May 12 at 12:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.