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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 update

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:
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
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 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
[/code]


--
Chandu
Go to Top of Page

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-11-28 : 10:17:53
thanks
Go to Top of Page
   

- Advertisement -