基于同一表的子查询更新表中的行 [英] 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屋!