Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

arkiboys
Flowing Fount of Yak Knowledge

1433 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
2241 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

1433 Posts

Posted - 11/28/2012 :  10:17:53  Show Profile  Reply with Quote
thanks
Go to Top of Page
  Previous Topic Topic Next 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.03 seconds. Powered By: Snitz Forums 2000