Help with the Query

Last Post 21 Aug 2014 12:08 PM by vayumahesh. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
vayumahesh
New Member
New Member

--
20 Aug 2014 02:22 PM
I have some tables and data as follows.

Table t1

DocumentID OlDDocumentID
1 NULL
2 NULL
3 NULL
5 NULL
7 NULL
8 NULL


Table t2

DocumentDeptID DocumentID DepartmentID
1 1 1
2 2 1
3 3 1
4 3 2
5 5 1
6 5 2
7 7 1
8 8 2

Table t3

DocumentDeptSubID DocumentDeptID SubID OldDocumentDeptID
1 1 1 NULL
2 1 2 NULL
3 1 3 NULL
4 2 1 NULL
5 2 2 NULL
6 3 1 NULL
7 3 2 NULL
8 4 1 NULL
9 4 2 NULL
10 5 1 3
11 5 2 3
12 6 1 4
13 6 2 4
14 7 1 NULL
15 7 2 NULL
16 8 1 NULL

I would like to populate the OldDocumentDeptID from table t3 to OldDocumentID in table t1 by joining on t1.DocumentID = t3.DocumentDeptID

After the update query, the result should be as follows.

Table t1

DocumentID OlDDocumentID
1 NULL
2 NULL
3 NULL
5 3
7 NULL
8 NULL

Can anyone please help with the query to accomplish the above ?
vayumahesh
New Member
New Member

--
21 Aug 2014 12:08 PM
I figured it out by myself. Here is the solution so that it may help someone.


UPDATE t1
SET t1.OldDocumentID = s.OldDocumentDeptID
FROM t1
INNER JOIN
(
SELECT DISTINCT DocumentDeptID, OldDocumentDeptID
FROM t3
WHERE t3.OldDocumentDeptID IS NOT NULL
) AS s
ON t1.DocumentID = s. DocumentDeptID
You are not authorized to post a reply.

Acceptable Use Policy