CODEWITHSUNDEEP
sqlpostgresql
J2112O
J2112O

Reputation: 635

Update multiple columns in table with SET command

I don't necessarily have an error, mainly curious as to what is going on behind the scenes with an UPDATE I am trying to perform on multiple columns of the same table on a single record, using the value of one column and some math to set the value in the other column. Basically a two for one is what I am trying to accomplish. I have this row in a table named pipe_tally3:

 id |  jt_id   | man_jt_num |  heat  | lt |   manu   | wall_thick | diam | grade | coat_type | coat_thick | loc  | cuts | new_lt | pup | notes |    date    
----+----------+------------+--------+----+----------+------------+------+-------+-----------+------------+------+------+--------+-----+-------+------------
  2 | Asset-33 | 1919A      | 39393b | 74 | american |      0.393 |   10 | x44   | stiff     | 12 mils    | yard |    0 |      0 | na  | na    | 2017-03-27

I try to UPDATE it like this: 

UPDATE pipe_tally3
SET cuts = 2.0, new_lt = lt - cuts
WHERE jt_id = 'Asset-33';
UPDATE 1

Then when I check that row again:

 id |  jt_id   | man_jt_num |  heat  | lt |   manu   | wall_thick | diam | grade | coat_type | coat_thick | loc  | cuts | new_lt | pup | notes |    date    
----+----------+------------+--------+----+----------+------------+------+-------+-----------+------------+------+------+--------+-----+-------+------------
  2 | Asset-33 | 1919A      | 39393b | 74 | american |      0.393 |   10 | x44   | stiff     | 12 mils    | yard |    2 |     74 | na  | na    | 2017-03-27

The cuts column did get updated and is now showing the value 2 but the new_lt columns shows the value 74. But it should be (or I want it set to) the value of 72, which would be the value of the lt column 74 subtracted from the cuts column of 2. Why does this not work like this is my main question? But if I update the cuts column by itself with

UPDATE pipe_tally3
SET cuts = 3.0 WHERE jt_id = 'Asset-33';
UPDATE 1

And it does get updated.

id |  jt_id   | man_jt_num |  heat  | lt |   manu   | wall_thick | diam | grade | coat_type | coat_thick | loc  | cuts | new_lt | pup | notes |    date    
----+----------+------------+--------+----+----------+------------+------+-------+-----------+------------+------+------+--------+-----+-------+------------
  2 | Asset-33 | 1919A      | 39393b | 74 | american |      0.393 |   10 | x44   | stiff     | 12 mils    | yard |    3 |     72 | na  | na    | 2017-03-27

I can then use 

UPDATE pipe_tally3
SET new_lt = lt - cuts WHERE jt_id = 'Asset-33';
UPDATE 1

And it works as desired by using the values from the lt and cuts columns when it sets the new_lt:

id |  jt_id   | man_jt_num |  heat  | lt |   manu   | wall_thick | diam | grade | coat_type | coat_thick | loc  | cuts | new_lt | pup | notes |    date    
----+----------+------------+--------+----+----------+------------+------+-------+-----------+------------+------+------+--------+-----+-------+------------
  2 | Asset-33 | 1919A      | 39393b | 74 | american |      0.393 |   10 | x44   | stiff     | 12 mils    | yard |    3 |     71 | na  | na    | 2017-03-27

Is there a way I can accomplish this in PostgreSQL? Thanks in advance.

Edit I realize I can always do this by hard coding the value in as well:

UPDATE pipe_tally3
SET cuts = 3.5, new_lt = lt - 3.5 
WHERE jt_id = 'Asset-33';
UPDATE 1

And that works

id |  jt_id   | man_jt_num |  heat  | lt |   manu   | wall_thick | diam | grade | coat_type | coat_thick | loc  | cuts | new_lt | pup | notes |    date    
----+----------+------------+--------+----+----------+------------+------+-------+-----------+------------+------+------+--------+-----+-------+------------
  2 | Asset-33 | 1919A      | 39393b | 74 | american |      0.393 |   10 | x44   | stiff     | 12 mils    | yard |  3.5 |   70.5 | na  | na    | 2017-03-27

Just wanted to know why it does not work (or I am not doing something correctly) the other way as specified above.

Upvotes: 2

Views: 13961

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125444

update pipe_tally3
set cuts = s.cuts, new_lt = lt - s.cuts
from (
    select 2.0 as cuts
) s
where jt_id = 'asset-33';

Upvotes: 2

Alex K.
Alex K.

Reputation: 175896

As you observe = lt - cuts uses the value of cuts before its been updated which is 74 - 0 so the value remains 74.

This is the behaviour in most databases, describing the expression value for set the documentation confirms this:

An expression to assign to the column. The expression can use the old values of this and other columns in the table.

Upvotes: 2

Related Questions