Find out difference, and then change boolean in one query

Question

I have one column. show measurements. And I want to find out the min and the max difference. Then if the difference more than 100 I like to change one another column on different table boolean troue to false. If less than 100 no change.

The "t1" column on the "Table_1_Furnace_1" table column. Same database have the another table, "StatusTable" and one boolean column "Status_Column".

If the difference more than 100 then need to change the status to true.

My half query is:

select max("t1") - min("t1") as difference 
from "Table_1_Furnace_1";

This is found the difference between min and max. But how can I add how it the difference more than 100 change the boolean column to true.

Idea but not work:

select max("t1") - min("t1") as difference 
from "Table_1_Furnace_1"

update "statusTable" 
  set "Status_Column" = true 
where difference > 100;

---

Let me try to explain better.

Measurements table:

ID	measurements
1	200
2	200
3	210
4	100
5	500
6	600
7	800
8	550

to 700 ID and measurements. all Integer value.

Status table: Status column boolean. Default False.

I need to choose 2 ID. and under the ID same row have one measurement. example: Choose ID 1 and ID 8. That is 8 ID and same row 5 different measurement.

ID	measurements
1	200
2	200
3	210
4	100
5	500
6	600
7	800
8	550

We need to find the minimum ( ID 4 ) and need to find the maximum ( ID 7 ) If the minimum the ID 4 that case we not care after the ID-s before ( 1-2-3 ). After need to find out the difference between min and max. If the minimum have before ( ID 4 ) and the maximum after ( ID 7 ) and the difference more than 100 that case need to change the Status boolean to true. If less than, or the maximum have before and then the minimum ( the temperature go lower not higher ) that case also not need to change true. This is can be one single query or automatized if possible.

Answer 1

Just take the select statement

select max("t1") - min("t1") as difference 
from "Table_1_Furnace_1"

and substitute it for the fictitious difference reference in your update statement, enclosing it in parentheses too, like this:

update "statusTable" 
  set "Status_Column" = true 
where
  (
    select max("t1") - min("t1") as difference
    from "Table_1_Furnace_1"
  ) > 100;

Written this way, a select query effectively becomes a scalar expression and can thus be used in contexts where expressions are allowed. In this case, you can use it in a comparison against a value.

Note that when a subquery is used as a scalar expression, only its result is relevant. What that means is that assigning an alias to the column inside the subquery is unnecessary. The above statement will still work if you omit the as difference bit:

update "statusTable" 
  set "Status_Column" = true 
where
  (
    select max("t1") - min("t1")
    from "Table_1_Furnace_1"
  ) > 100;

Answer 2

Table_1_Furnace_1

Do you have lots of Tables, one for each Furnace?
The "Table-per-Thing" Model is generally a Bad Idea, does not scale well and makes for aggregation across "Things" very, very difficult.

Better to have one, properly indexed, table for all of your Furnaces.

how can I add how it the difference more than 100 change the boolean column to true.

Why store this value at all? Since you can calculate this value, you should probably base any analysis on that calculation (unless that calculation is really, really slow). For much the same reason, you don't store people's Age - you store their [fixed] Date-of-Birth and calculate their [dynamic] Age based on that.