CODEWITHSUNDEEP
sqlpostgresql
Jane Borges
Jane Borges

Reputation: 592

How to assign the result of a SELECT as a table column in sql?

I have the following table (table_test) in the postgres database:

      id         value         description
     -----------------------------------------
     741         1             'description X'
     152         2             'description X'
     555         1             'description X'

I need to create a new column in this table. The new column should receive the column value 'value' multiplied by 1000 and divided by 3.

First I created a new column called 'new_column'

ALTER TABLE table_test
    ADD new_column float(10)

Next, I did the desired operation with a SELECT:

SELECT ((value * 1000) / 3)
FROM table_test

The problem arose when I tried to duplicate the SELECT result in the newly created column. I tried to do (not working):

UPDATE table_test
SET new_column = (SELECT ((value * 1000) / 3)
                  FROM table_test)

The error message that appears is:

ERROR: more than one row returned by a subquery used as an expression
SQL state: 21000

I would like the output to be:

        id         value          description         new_column
        --------------------------------------------------------
        741         1             'description X'        333
        152         2             'description X'        666
        555         1             'description X'        333

Upvotes: 0

Views: 396

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You can add a computed column instead:

ALTER TABLE table_test ADD new_column float GENERATED ALWAYS AS
    (value * 10000 / 3.0) stored;

This means the value is always up-to-date.

If you just want to set the value, though, the update for a non-generated column is simply:

UPDATE table_test
    SET new_column = value * 1000 / 3.0;

Upvotes: 2

Related Questions