SQL - Calculating sum of variable updated values

Question

I'm new to SQL and I have the following problem.
I want to sum total number of errors that are reaching my device.

So, suppose I have inside my_table variables with names SeqErr and TotalSeqErr.
I initialized them to 0 and verified it, but when I do the following:

  UPDATE my_table
  SET
  ...
    SeqErr         =      COALESCE(SeqErr, 0) + 1,
    TotalSeqErr    =      SeqErr,
  ...

I see that when I retrieve this table it is coming with the following values:
SeqErr = 1 , TotalSeqErr = 0 (i.e. SeqErr was updated but TotalSeqErr is using old SeqErr value instead of updated one).

How can I solve this issue?
Note: Probably there is issue similar to mine here but I couldn't find one.

I'm using PostgreSQL as my SQL database if this is what you meant. — ms_stud
– ms_stud, Commented Oct 7, 2020 at 9:37
You will need to run that UPDATE at least twice to see a non-zero value in totalseqerr (but that assignment should also use coalesce()) dbfiddle.uk/… — user330315
– user330315, Commented Oct 7, 2020 at 9:43

Gabriel Durac · Accepted Answer · 2020-10-07 09:50:32Z

1

If you want your TotalSeqErr to use the "new" SeqErr you could just update it the same way you update SeqErr:

...
SeqErr         =      COALESCE(SeqErr, 0) + 1,
TotalSeqErr    =      COALESCE(SeqErr, 0) + 1,
...

You could imagine that update is done all at once not line by line. So what you have in the right side of the equal refers to the old values even though you set them to a new value a line above.

edited Oct 7, 2020 at 9:50

answered Oct 7, 2020 at 9:45

Gabriel Durac

2,7681 gold badge14 silver badges13 bronze badges

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

3 Comments

ms_stud Over a year ago

So there is no way to do so inside one query? (In case I want TotalSeqErr to get SeqErr updated variable I will need it to be inside another query)

user330315 Over a year ago

@ms_stud: that is one query.

Gabriel Durac Over a year ago

Added an explanation to my answer, let me know if that helps or if you require extra informaton. Note that COALESCE(SeqErr, 0) + 1 is not another query, it is another expression.

Gordon Linoff · Accepted Answer · 2020-10-07 11:24:35Z

The way an update works is that the references on the "left" (i.e. the set columns) refer to the new record. The references on the "right" (i.e. the expressions) refer to the old values. This is how SQL is defined. I only know of one database that does not support the standard behavior, MySQL/MariaDB.

One method is to repeat the expression:

UPDATE my_table
  SET . . .
      SeqErr      =  COALESCE(SeqErr, 0) + 1,
      TotalSeqErr =  COALESCE(SeqErr, 0) + 1,
      ...;

Assuming your table has a primary key, you can also simplify the SET logic by using a FROM clause and a lateral join:

UPDATE my_table t
      SET . . .
          SeqErr      =  v.new_SeqErr,
          TotalSeqErr =  v.new_SeqErr,
          ...
    FROM my_table t2 CROSS JOIN LATERAL
         (VALUES (COALESCE(t2.SeqErr, 0) + 1)
         ) v(new_seqerr)
    WHERE t2.<primary key> = t.<primary key>

Collectives™ on Stack Overflow

SQL - Calculating sum of variable updated values

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related