基于同一表的子查询更新表中的行 [英] Updating row in table based on sub query on same table

问题描述

我在tableA"中有一个列，我想用以下查询的结果进行更新.基本上，第 5 个日期列将是同一行其他 4 个列中的最大日期.

I have a column in 'tableA' which i would like to update with the result from the following query. Basically the 5th date column will be the maximum date from the other 4 columns on the same row.

select  
    Case
        when date1 >= date2 and date1 >= date3 and date1 >=date4 then date1

        when date2 >= date1 and date2 >= date3 and date2 >=date4 then date2

        when date3 >= date1 and date3 >= date2 and date3 >=date4 then date3

        when date4 >= date1 and date4 >= date2 and date4 >=date3 then date4
    End as Date5
from
tableA      

但是，我无法在更新语句中获得正确的语法，因为我遇到了多行错误或其他类型的错误.例如

However I can't get the syntax right in the in the update statement as I get either a multiple rows error or some other type of error. e.g.

update tableA a
set Date5 = (
    select  
        Case
            when date1 >= date2 and date1 >= date3 and date1 >=date4 then date1

            when date2 >= date1 and date2 >= date3 and date2 >=date4 then date2

            when date3 >= date1 and date3 >= date2 and date3 >=date4 then date3

            when date4 >= date1 and date4 >= date2 and date4 >=date3 then date4
        End as Date5
    from
    tableA b)
where A.ID = B.ID

感谢您的帮助！

下方更新

经过更多研究后，我决定采用不同的解决方案来获取最大日期，例如

After some more research I decided to go about a different solution for getting the max date e.g.

    Select Max(dDate) maxDate, ID
      From tableA
           Unpivot (dDate FOR nDate in (Date1, Date2, Date3)) as u
     Group by ID

不幸的是，这意味着我原来的问题仍然存在，即如何将上述查询组合为更新中的子查询.

Unfortunately this means my original question still stands ie how do you combine the above query as a subquery within an update.

推荐答案

最简单的方法是

UPDATE tableA
SET    date5 = (SELECT MAX(D)
                FROM   (VALUES(date1),
                              (date2),
                              (date3),
                              (date4)) T(D))  

或者(因为您现在已经添加了 2005 标签)

Or (as you have now added the 2005 tag)

UPDATE tableA
SET    date5 = (SELECT MAX(D)
                FROM   (SELECT date1 UNION ALL
                        SELECT date2 UNION ALL
                        SELECT date3 UNION ALL
                        SELECT date4) T(D))  

这篇关于基于同一表的子查询更新表中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！