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?
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
> 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?
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
> 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
>> 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?
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.
> 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
> >> 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?
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.
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.
|
