0

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.

Improve this question
2
  • The question doesn't make a lot of sense. You want to update a different table "statusTable". How is "statusTable" related to "Table_1_Furnace_1"? Commented Dec 14, 2022 at 12:50
  • The status table only have one Status column and one ID column only one row. ( ID 1 and Statuse False ). If any time the measurements go up high more than 100 degree difference need to change that only one column only one row, because that case critical temperature change happened. If the measurements go low more than 100 that case not. Commented Dec 16, 2022 at 5:57

2 Answers 2

Reset to default
0

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;
Improve this answer
1
  • Thank you for your solution. Another thing. All of the 700 measurements have ID ( 1-700) If I want to pick two. Like pick ID2 and ID 22. This case just check the measurements between this 2 ID ( 2-22 ) and if have more than 100 difference Up! that case change to true. If between this 2 measurements before have the highest and then lovest but still ahve 100 difference not need to cahnge to true because the temp go lower. Commented Dec 15, 2022 at 8:50
0

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.

Improve this answer
2
  • I have one furnace table, and in the furnace table have t1 to t12 colums. All colums have 700 measurements. First in the t1 need to find the minimum and the maximum and like written down above calculate the difference. And this measurements fix. Not change later. If the diference more than 100 then change the Status true. Status table only have 6 column. ID and 5 different status. But we only work with one status no from the 5 what we need to change id more than 100 the difference. Commented Dec 14, 2022 at 14:31
  • Is this a one-time action or do you want it to happen "automatically"? If it's one-time, then run your query and if it returns more than 100, run your update. if you want any kind of "automation" - not sure you would if there's no "change later" - then you need to decide what triggers this activity and put the code there. Read up on Postgres's IF statement. Commented Dec 15, 2022 at 12:16

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.