Thursday, 30 August 2018

Sql to update several columns with non-zero values ​​of another table

I have the following table (table1) structure as the result of a select case query

server      timestamp   ftpSuccRate httpSuccRate
123.2.3.2   1:00 am     1       null
123.2.3.2   1:00 am     null        0.5

I want to update another table's (table 2) that has the following structure with values from table1
server      timestamp   ftpSuccRate httpSuccRate
123.2.3.2   1:00 am     1           0.5

Basically I want to update ftpsuccrate of table2 with the not null ftpSuccRate value of table1 . Same for httpSuccRate. The join criteria betweent table1 and table2 is server and tiemstamp

UPDATE table2

SET table2.ftpSuccRate = CASE
                              WHEN table1.ftpSuccRat IS NULL TEHN
                                     table2.ftpSuccRate
                              ELSE
                                      table1.ftpSuccRat
                         END.
table2.httpSuccRate = CASE
                              WHEN table1.httpSuccRate IS NULL TEHN
                                     table2.httpSuccRate
                              ELSE
                                      table1.httpSuccRate
                         END

FROM (your case select here) AS table1
WHERE table2.server = table1.server AND table2.timestamp = table1.timestamp

0 comments:

Post a Comment