Updating row in table based on sub query on same t

问题:

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

Thanks for any help!

---

Update below

---

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.

回答1:

Simplest way of doing this would be

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

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))  

回答2:

You don't need the subquery:

update tableA a
   set Date5 = 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

回答3:

You can join the result of the query back to the original table and update the latter using the join. Here's how:

UPDATE a
SET Date5 = s.maxDate
FROM TableA a
  INNER JOIN (
    Select Max(dDate) maxDate, ID
      From tableA
           Unpivot (dDate FOR nDate in (Date1, Date2, Date3)) as u
     Group by ID
  ) s ON a.ID = s.ID