| Author |
 Topic  |
|
|
sql_server_dba
Posting Yak Master
137 Posts |
 Posted - 03/19/2013 : 22:07:52
|
How can we update the latest Account for all the old accountID's?
Source Table
AccountID ToAccountID
1 2
2 3
3 4
4 5
11 66
22 66
Target table
AccountID ToAccountID LatestAccountID
1 2 5
2 3 5
3 4 5
4 5 5
11 66 66
22 66 66 |
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 03/20/2013 : 07:44:11
|
CREATE TABLE #Source (accountID tinyint,ToAccountID tinyint)
CREATE TABLE #Target (accountID tinyint,ToAccountID tinyint,LatestAccountID tinyint)
INSERT INTO #Source
VALUES
( 1, 2),
( 2, 3),
( 3, 4),
( 4, 5),
( 11, 66),
( 22, 66)
INSERT INTO #Target(accountID,ToAccountID)
VALUES
( 1, 2),
( 2, 3),
( 3, 4),
( 4, 5),
( 11, 66),
( 22, 66)
; with src(AccountID,ToAccountID)
as
(
select s1.AccountID,s1.ToAccountID
from #source s1
union all
select s1.AccountID,s2.ToAccountID
from src s2
inner join #source s1 on s2.AccountID = s1.ToaccountID
)
UPDATE tgt
SET LatestAccountID = t1.LatestAccountID
FROM #Target tgt
INNER JOIN
(
select AccountID,MAX(ToAccountID) as LatestAccountID
from src
group by AccountID
) t1 on tgt.AccountID = t1.AccountID
select *
from #target
Jim
Everyday I learn something that somebody else already knew |
 |
|
|
UnemployedInOz
Yak Posting Veteran
Australia
52 Posts |
Posted - 03/20/2013 : 18:34:30
|
CREATE TABLE #Source (accountID tinyint,ToAccountID tinyint)
CREATE TABLE #Target (accountID tinyint,ToAccountID tinyint,LatestAccountID tinyint)
INSERT INTO #Source
VALUES
( 1, 2),
( 2, 3),
( 3, 4),
( 4, 5),
( 11, 66),
( 22, 66)
go
Merge Into #Target as TGT
Using #Source as SRC
ON TGT.accountID = SRC.accountID
When NOT Matched then
Insert Values (SRC.accountID,ToAccountID,null)
WHEN Matched AND TGT.ToAccountID <> SRC.ToAccountID then
Update SET
TGT.LatestAccountID = TGT.toAccountID,
TGT.ToAccountID = SRC.ToAccountID;
go |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 03/21/2013 : 02:20:23
|
quote:
Originally posted by UnemployedInOz
CREATE TABLE #Source (accountID tinyint,ToAccountID tinyint)
CREATE TABLE #Target (accountID tinyint,ToAccountID tinyint,LatestAccountID tinyint)
INSERT INTO #Source
VALUES
( 1, 2),
( 2, 3),
( 3, 4),
( 4, 5),
( 11, 66),
( 22, 66)
go
Merge Into #Target as TGT
Using #Source as SRC
ON TGT.accountID = SRC.accountID
When NOT Matched then
Insert Values (SRC.accountID,ToAccountID,null)
WHEN Matched AND TGT.ToAccountID <> SRC.ToAccountID then
Update SET
TGT.LatestAccountID = TGT.toAccountID,
TGT.ToAccountID = SRC.ToAccountID;
go
It is not giving the correct output.... (as per your solution, LatestAccountID=NULL for all AccountIDs)
--
Chandu |
|
|
|
UnemployedInOz
Yak Posting Veteran
Australia
52 Posts |
Posted - 03/21/2013 : 07:33:41
|
When the data changes for an ID in the source table the LastAccountID will be populated when running the Merge with the OLD TGT.toAccountID.
|
|
|
| |
Topic |
|
|
|
ADD and Update to latest Record
