Select中的Oracle Update-相同表 [英] Oracle Update from Select - same table

问题描述

我正在尝试使用date2，date3，date4列中具有最高日期的date1列进行更新. 我有选择声明

I'm trying to update column date1 with highest date from column date2,date3,date4. I have select statement

SELECT GREATEST(
    COALESCE(date2, date3, date4),
    COALESCE(date3, date4, date2),
    COALESCE(date4, date2, date3)
)
FROM (
    SELECT date1,date2,date3,date4 
    FROM my_table
    WHERE date1 is not null and (date1 < date2 or date1 < date3 or date1 < date4);

但是我不知道如何在更新或合并中使用它?

But I do not know how to use it with update or merge ??

+------------------+------------------+------------------+------------------+ | date1 | date2 | date3 | date4 | +------------------+------------------+------------------+------------------+ | 2017-04-13 16:54 | 2017-04-13 16:57 | | 2016-06-16 | | 2017-04-13 15:41 | | 2017-04-13 15:42 | 2016-06-16 | | 2017-04-13 15:43 | 2018-01-10 14:23 | 2017-04-13 15:41 | | | 2017-04-13 16:05 | | 2017-04-13 16:05 | 2016-06-16 | | 2017-04-13 16:43 | 2017-04-13 16:43 | | 2016-06-16 | | 2017-04-13 16:52 | 2017-04-13 16:52 | 2017-04-13 16:07 | 2017-04-17 16:07 | | 2018-01-10 14:20 | | 2018-01-10 14:23 | | | 2017-09-27 14:54 | 2015-09-08 09:56 | 2017-09-27 14:54 | 2015-03-13 | | 2017-06-16 13:38 | | 2017-06-16 13:39 | | +------------------+------------------+------------------+------------------+

+------------------+------------------+------------------+------------------+ | date1 | date2 | date3 | date4 | +------------------+------------------+------------------+------------------+ | 2017-04-13 16:54 | 2017-04-13 16:57 | | 2016-06-16 | | 2017-04-13 15:41 | | 2017-04-13 15:42 | 2016-06-16 | | 2017-04-13 15:43 | 2018-01-10 14:23 | 2017-04-13 15:41 | | | 2017-04-13 16:05 | | 2017-04-13 16:05 | 2016-06-16 | | 2017-04-13 16:43 | 2017-04-13 16:43 | | 2016-06-16 | | 2017-04-13 16:52 | 2017-04-13 16:52 | 2017-04-13 16:07 | 2017-04-17 16:07 | | 2018-01-10 14:20 | | 2018-01-10 14:23 | | | 2017-09-27 14:54 | 2015-09-08 09:56 | 2017-09-27 14:54 | 2015-03-13 | | 2017-06-16 13:38 | | 2017-06-16 13:39 | | +------------------+------------------+------------------+------------------+

更新/合并后，我想要类似的东西

After update/merge I would like something like that

+------------------+------------------+------------------+------------------+ | date1 | date2 | date3 | date4 | +------------------+------------------+------------------+------------------+ | 2017-04-13 16:57 | 2017-04-13 16:57 | | 2016-06-16 | | 2017-04-13 15:42 | | 2017-04-13 15:42 | 2016-06-16 | | 2018-01-10 14:23 | 2018-01-10 14:23 | 2017-04-13 15:41 | | | 2017-04-13 16:05 | | 2017-04-13 16:05 | 2016-06-16 | | 2017-04-13 16:43 | 2017-04-13 16:43 | | 2016-06-16 | | 2017-04-17 16:07 | 2017-04-13 16:52 | 2017-04-13 16:07 | 2017-04-17 16:07 | | 2018-01-10 14:23 | | 2018-01-10 14:23 | | | 2017-09-27 14:54 | 2015-09-08 09:56 | 2017-09-27 14:54 | 2015-03-13 | | 2017-06-16 13:39 | | 2017-06-16 13:39 | | +------------------+------------------+------------------+------------------+

+------------------+------------------+------------------+------------------+ | date1 | date2 | date3 | date4 | +------------------+------------------+------------------+------------------+ | 2017-04-13 16:57 | 2017-04-13 16:57 | | 2016-06-16 | | 2017-04-13 15:42 | | 2017-04-13 15:42 | 2016-06-16 | | 2018-01-10 14:23 | 2018-01-10 14:23 | 2017-04-13 15:41 | | | 2017-04-13 16:05 | | 2017-04-13 16:05 | 2016-06-16 | | 2017-04-13 16:43 | 2017-04-13 16:43 | | 2016-06-16 | | 2017-04-17 16:07 | 2017-04-13 16:52 | 2017-04-13 16:07 | 2017-04-17 16:07 | | 2018-01-10 14:23 | | 2018-01-10 14:23 | | | 2017-09-27 14:54 | 2015-09-08 09:56 | 2017-09-27 14:54 | 2015-03-13 | | 2017-06-16 13:39 | | 2017-06-16 13:39 | | +------------------+------------------+------------------+------------------+

推荐答案

您应该可以:

update t
    set date1 = greatest(date1, coalesce(date2, date1), coalesce(date3, date1), coalesce(date4, date1))
    where date1 < greatest(date1, coalesce(date2, date1), coalesce(date3, date1), coalesce(date4, date1));

在您的数据中，date1似乎永远不会是NULL.

In your data, date1 appears to never be NULL.

这篇关于Select中的Oracle Update-相同表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！