CODEWITHSUNDEEP
sqlpostgresqlsql-update
Daniel Gadawski
Daniel Gadawski

Reputation: 1923

How to update two columns in one query, one from another?

Is it possible to update two columns, one of them from another's value in one PostgreSQL query?

Let me explain it. If I had this table:

CREATE TABLE test
(
  id bigint,
  val1 integer,
  val2 integer
);

and insert there a row:

INSERT INTO test (id, val1, val2) VALUES (1, NULL, NULL);

Then I need to update val1 and val2 columns. For val1 I set explicit value, for val2 I use val1 column's value.

UPDATE test
SET 
  val1 = 1,
  val2 = val1
WHERE id = 1

I expect to set val1 to 1 and val2 to 1 as a reference to just set val1. However, after this update I get 1 in val1 column and NULL in val2.

Of course, it isn't real life example. In my real problem I set val1 value with several conditions, so I wouldln't like to copy part of them again to val2 value.

Is it possible to do this like I expect in one query?

Upvotes: 0

Views: 38

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

If you don't want to repeat conditions, you can define them in the query. For instance:

UPDATE test
    SET val1 = v.new_val1,
        val2 = v.new_val1
    FROM (VALUES (1)) v(new_val1)
    WHERE id = 1;

Upvotes: 1

Related Questions