SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 update
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

arkiboys
Flowing Fount of Yak Knowledge

1406 Posts

Posted - 11/28/2012 :  07:08:42  Show Profile  Reply with Quote
I would like to update a field in one table based on the details of another table as follows:
tblData
TransferID SystemID ToSystemID TransferDate
1 15 30 09 sep 2011
2 8 2 10 jan 2008
...
...

tblMain
CustID IncDate SystemID
11 15 feb 2007 15
11 18 jan 2010 30
7 26 nov 2000 3
3 12 Dec 2000 2
3 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:

tblMain
CustID IncDate SystemID
11 15 feb 2007 15
11 09 sep 2011 30
7 26 nov 2000 3
3 10 jan 2008 2
3 18 march 1999 8
...
How is this done in sql please?
Thanks

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 11/28/2012 :  08:19:21  Show Profile  Reply with Quote

DECLARE @tblData TABLE(TransferID int, SystemID int, ToSystemID int, TransferDate date)
insert into @tblData 
SELECT 1, 15, 30, '09/09/2011' union all
SELECT 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 all
SELECT 11, '18/01/2010', 30 union all
SELECT 7, '26/11/2000', 2 union all
SELECT 3, '12/12/2000', 2 union all
SELECT 3, '18/03/1999', 8

--SELECT CustID, d.SystemID, m.SystemID, d.ToSystemID,IncDate, TransferDate
UPDATE m 
SET IncDate = TransferDate
FROM @tblData d JOIN @tblMain m ON d.ToSystemID = m.SystemID
WHERE CustID IN (SELECT CustID FROM @tblMain GROUP BY CustID HAVING COUNT(*) = 2)

SELECT * FROM @tblMain



--
Chandu
Go to Top of Page

arkiboys
Flowing Fount of Yak Knowledge

1406 Posts

Posted - 11/28/2012 :  10:17:53  Show Profile  Reply with Quote
thanks
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000