update table dateCol3 to the later of dateCol1 or dateCol2

[Rich]

CREATE TABLE #tmp1(rowID int identity(1,1), dateCol1 datetime, dateCol2
datetime, dateCol3 datetime)

INSERT INTO #tmp1(dateCol1, dateCol2)
SELECT '1/1/05', '2/1/05'
UNION ALL SELECT '3/1/05', '3/7/05'
UNION ALL SELECT '4/1/05', '3/20/05'
UNION ALL SELECT '5/1/05', '5/13/05'

UPDATE #tmp1 SET dateCol3 = ?

If dateCol1 > dateCol2 then update dateCol3 to dateCol1
else
update #tmp1 Set dateCol3 to dateCol2

I am sure this is not the most normalized example, but what would be the
tSql to update my table with the latest date in a row without having to do it
in 2 queries?

I realize I could say

update #tmp1 set datecol3 = datecol1 where datecol1 > datecol2

and then

update #tmp1 set datecol3 = datecol2 where datecol2 > datecol1

Is there a way to do this in one query statement? What would that look like?

Thanks,
Rich

[Rich]

Here is something I came up with that seems to work

UPDATE #tmp1 SET datecol3 = t2.dateR FROM #tmp1 t1 JOIN
(Select * FROM
(Select RowID, datecol1 dateR FROM #tmp1 WHERE datecol1 > datecol2
union all select RowID, datecol2 dateR From #tmp1 where datecol2 >
datecol1) tA) t2
on t1.rowID = t2.rowid

[Kalen Delaney]

Hi Rich

Using a UNION in your subquery is in effect just as much work for SQL Server
as two queries. Plus you have two levels of subquery plus a JOIN when you
just need to access a single table one time. Sure, it might 'work', but at
what cost?

How about using a simple CASE expression, with no JOIN and no subqueries?

UPDATE #tmp1 SET dateCol3 =

CASE
WHEN dateCol1 > dateCol2 THEN dateCol1
ELSE dateCol2
END

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com

"Rich" <Ri...@discussions.microsoft.com> wrote in message
news:A1E1C26F-66D9-48CB...@microsoft.com...

[Rich]

Your idea is way better than my idea! Thanks for the help. That is sort of
what I thought would be the way to do it, but either I was too lazy to think
it up or I was not brave enough.

Thanks.

[--CELKO--]

If you followed ISO-11179 data element naming rules, avoided
needlessly proprietary code, and put your date into the proper format
for Standard SQL, would your posting look like this?

CREATE TABLE Foobar
(foobar_id INTEGER NOT NULL PRIMARY KEY,
col1_date DATETIME NOT NULL,
col2_date DATETIME NOT NULL,
col3_date DATETIME);

INSERT INTO Foobar(foobar_id, col1_date, col2_date)
VALUES (42, '2005-01-01', '2005-02-01');
Etc.

>>
IF col1_date > col2_date
THEN update col3_date to col1_date
ELSE update col3_date to col2_date
<<

In SQL, you have to watch out for the NULLs, which is why your
original DDL had problems I fixed (did I guess right?). Now you can
write a SET clause or statement like this:

SET col3_date =
CASE WHEN col1_date > col2_date
THEN col1_date
ELSE col2_date END;

You have a few choices here.
1) You can make col3_date into a computed column in proprietary syntax
-- look it up.
2) You can use the CASE expression to update col3_date; "UPDATE Foobar
SET col3_date = CASE.. END;" Of course this still means that you have
materialized computed data in violate of good design.
3) You can put it in a VIEW, and drop col3_date from the base table.
The code will always be right and portable.

[Tony Rogerson]

> INSERT INTO Foobar(foobar_id, col1_date, col2_date)
> VALUES (42, '2005-01-01', '2005-02-01');
> Etc.

Yet again even after seeing countless examples you still persist in using
syntax that is inconsistent in countries that don't use US English.

Please be professional and desist!

With the default server settings for all servers installed and used in the
UK (British English) this gives inconsistent results...

select cast( '2007-04-01' as datetime )
select cast( '2007-04-01T00:00:00' as datetime )

The correct way (AND STILL A STANDARD FORMAT) is to use T as the seperator.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]

[Tony Rogerson]

Thought about using a computed column instead? Why actually store this?

CREATE TABLE #tmp1(rowID int identity(1,1), dateCol1 datetime, dateCol2

datetime, dateCol3 as case when dateCol1 > datecol2 then datecol1 else
datecol2 end )

set dateformat mdy

INSERT INTO #tmp1(dateCol1, dateCol2)
SELECT '1/1/05', '2/1/05'
UNION ALL SELECT '3/1/05', '3/7/05'
UNION ALL SELECT '4/1/05', '3/20/05'
UNION ALL SELECT '5/1/05', '5/13/05'

select *
from #tmp1

PS...

You need to specify date formats differently; your code doesn't work here in
the UK. Specify dates YYYY-MM-DDTHH:MM:SS to give consistent across all
regions.

Tony.