Need SQL to select MAX datetime joining 2 tables
-
Need SQL to accomplish an UPDATE to TABLE_A's col_3 datetime w/ TABLE_B's max(col_4)
where TABLE_A col_1 & col_2 = TABLE_B col_1 & col_2 AND TABLE_B col_3 = 1
TABLE_A TABLE_B
col_1 int col_1 int
col_2 int col_2 int
col_3 datetime col_3 int
col_4 datetime
Data Values:
TABLE_A: col_1 col_2 col_4
155 137 1999-01-01 00:00:00.000
TABLE_B: col_1 col_2 col_3 col_4
155 137 1 2005-01-01 00:00:00.000
155 137 1 2002-01-01 00:00:00.000
155 137 1 1999-01-01 00:00:00.000
The following SQL is NOT guarantee'ing that I get the MAX TABLE_B col_4 datetime:
update TABLE_A set col_3 = col_4
from TABLE_A A, TABLE_B B
where A.col_1 = B.col_1
and A.col_2 = B.col_2
and B.col_3 = 1
BT -
Update A set A.col4 = dtMaxDates.Col4 from TableA a inner join
(Select col1, col2, col3, max(col4) as col4 from dbo.TableB group by Col1, Col2, col3) dtMaxDates
on A.col1 = dtMaxDates.col1 AND A.col2 = dtMaxDates.col2 AND A.col3 = dtMaxDates.col3
-
Remi - what a master of T-Sql you are...
I'm sure you were in a hurry so you omitted a couple of things...(that B.col_3 needed to be 1 and there was no join on col_3...)
Update Table_A Set Table_A.col_3 = dtMaxDates.col_4 from Table_A A inner join
(Select col_1, col_2, col_3, max(col_4) as col_4 from Table_B where col_3 = 1 group by col_1, col_2, col_3) dtMaxDates
on A.col_1 = dtMaxDates.col_1 AND A.col_2 = dtMaxDates.col_2
**ASCII stupid question, get a stupid ANSI !!!** -
Thanks! Much appreciated.
BT -
Oops... at least he got the idea
.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply