CODEWITHSUNDEEP
sqlpostgresql
Viraj Prateek
Viraj Prateek

Reputation: 5

Update minimum value column of selected rows

My table is-

## meters ##
meter_num--------reading1---------reading2-------reading3-------reading4
 123               12               14             15               10        
 345               14               15             16               19

For each row I want to update the minimum valued column to a given value (say zero). I know by using 'LEAST' function I can get the min value among the columns. But How do I update that minimum valued column from here? e.g. when I execute -

SELECT 
LEAST(reading1, reading2, reading3, reading4)
 FROM meters
 WHERE meter_num = '123'

returns -

reading4
  10

now I want to update reading4 to something else. How do I do that?

Upvotes: 0

Views: 107

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

Assuming there are no duplicates and none of the values are NULL, you can use case expressions:

update meters
    set reading1 = (case when reading1 = least(reading1, reading2, reading3, reading4) then 0 else reading1 end),
        reading2 = (case when reading2 = least(reading1, reading2, reading3, reading4) then 0 else reading2 end),
        reading3 = (case when reading3 = least(reading1, reading2, reading3, reading4) then 0 else reading3 end),
        reading4 = (case when reading4 = least(reading1, reading2, reading3, reading4) then 0 else reading4 end);

Note: This problem would be simpler if the readings were in separate rows rather than separate columns.

You can handle duplicates with a tweak:

update meters
    set reading1 = (case least(reading1, reading2, reading3, reading4)
                         when reading 1 then 0
                         else reading1
                   end),
        reading2 = (case least(reading1, reading2, reading3, reading4)
                         when reading1 then reading2
                         when reading2 then 0
                         else reading2
                    end),
        reading3 = (case least(reading1, reading2, reading3, reading4)
                         when reading1 then reading3
                         when reading2 then reading3
                         when reading3 then 0
                         else reading3
                    end),
       reading4 = (case least(reading1, reading2, reading3, reading4)
                         when reading1 then reading4
                         when reading2 then reading4
                         when reading3 then reading4
                         when reading4 then 0
                         else reading2
                    end),

Upvotes: 1

Related Questions