Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
davebatt
Starting Member
17 Posts |
 Posted - 2010-05-12 : 11:32:29
|
| Hello, I have two tables (I have left out unnecessary fields)table1 (can have many records for each user) ID userID datecreatedtable2 (can have many records for each user) ID userID datePlaced table1IDI need to update table2.table1ID with the ID from table1.I need to join on userID and then iterate through all matching records in table1 and select the id where datecreated is before dateplaced but the closet to it.If I havent explained it very well please let me know.Cheers Dave |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-05-12 : 11:35:10
|
| [code]UPDATE t2SET t2.table1ID = t1.IDFROM table2 t2CROSS APPLY (SELECT TOP 1 ID FROM table1 WHERE userID = t2.userID AND datecreated < t2.datePlaced ORDER BY datecreated DESC)t1[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
davebatt
Starting Member
17 Posts |
Posted - 2010-05-12 : 11:36:37
|
| Thankyou for the quick reply Visakh, Ill try it now and let you know. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-05-12 : 11:37:39
|
| ok..good luck------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
davebatt
Starting Member
17 Posts |
Posted - 2010-05-12 : 11:53:29
|
| Cheers mate thats fantastic, worked a charm |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-05-12 : 11:56:57
|
welcome  ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Joining tables based on date