When I am looking at two tables, I want to choose a maximum of 4 columns.

MERGE INTO TABLE_1 T1 USING TABLE_2 T2 ON T1.KEY_ID = T2.KEY_ID WHEN MATCHED THEN UPDATE SET T1.VALUE = MAX(T2.VALUE_1, T2.VALUE_2, T2.VALUE_3, T2.VALUE_4)

But in this form MAX will not work. In what form is it more correct to place?

  • four
    those. Do you need a maximum of 4 fields per line, not 4 lines? In this case there is a function greatest. Only NULL Don't Pass It - Mike
  • Thank you, Mike. Sorry to have NULLs cut off via NVL. Bulky, but better than nothing. At least one column will be exactly positive. - Vitaly Yandulov

1 answer 1

 MERGE INTO TABLE_1 T USING TABLE_2 t1 ON (T.KEY_ID = t1.KEY_ID) WHEN MATCHED THEN UPDATE SET T.VALUE_1 = GREATEST(NVL(T1.VALUE_1, 0), NVL(T1.VALUE_2, 0), NVL(T1.VALUE_3, 0), NVL(T1.VALUE_4, 0));
  • Yes, that's exactly what I did after Mike, thanks. - Vitaly Yandulov