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 |
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2012-11-28 : 07:08:42
|
I would like to update a field in one table based on the details of another table as follows:tblDataTransferID SystemID ToSystemID TransferDate1 15 30 09 sep 20112 8 2 10 jan 2008......tblMainCustID IncDate SystemID11 15 feb 2007 1511 18 jan 2010 307 26 nov 2000 33 12 Dec 2000 23 18 march 1999 8...I now would like to update tblMain to show as follows:basically, if there is CustID which has SystemID in (both SystemID and ToSystemID in tblData i.e. in this example: CustID = 11 or CustID = 3)set the IncDate to the TransferDate in tblData as follows:tblMainCustID IncDate SystemID11 15 feb 2007 1511 09 sep 2011 307 26 nov 2000 33 10 jan 2008 23 18 march 1999 8...How is this done in sql please?Thanks |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-11-28 : 08:19:21
|
[code]DECLARE @tblData TABLE(TransferID int, SystemID int, ToSystemID int, TransferDate date)insert into @tblData SELECT 1, 15, 30, '09/09/2011' union allSELECT 2, 8, 2, '10/01/2008'DECLARE @tblMain TABLE(CustID int, IncDate date,SystemID int)insert into @tblMain SELECT 11, '15/02/2007' ,15 union allSELECT 11, '18/01/2010', 30 union allSELECT 7, '26/11/2000', 2 union allSELECT 3, '12/12/2000', 2 union allSELECT 3, '18/03/1999', 8--SELECT CustID, d.SystemID, m.SystemID, d.ToSystemID,IncDate, TransferDateUPDATE m SET IncDate = TransferDateFROM @tblData d JOIN @tblMain m ON d.ToSystemID = m.SystemIDWHERE CustID IN (SELECT CustID FROM @tblMain GROUP BY CustID HAVING COUNT(*) = 2)SELECT * FROM @tblMain[/code]--Chandu |
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2012-11-28 : 10:17:53
|
thanks |
|
|
|
|
|
|
|