ADD and Update to latest Record

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 2012 Forums

Transact-SQL (2012)

ADD and Update to latest Record

Author

Topic

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2013-03-19 : 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
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-03-20 : 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

54 Posts

Posted - 2013-03-20 : 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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-21 : 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

54 Posts

Posted - 2013-03-21 : 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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources