January 27, 2016 at 12:50 pm
Here's the puzzle:
Update the name column in Table1 with the corresponding name in Table2.
1.The two tables must match on the AcctNo and WorkDate fields
2.The SeqNo cannot be counted on to match. (In the example,
the first records of each table match, but not in the remaining two records.)
3.In Table2, some records are like records 2 and 3 in the samples
where the AcctNo and WorkDate are the same, however, the names are
different (John vs Jane Doe) and the 2nd of the two records have a
SeqNo that is a value of ‘1’ greater than the first.
4.In the cases of these double records, I want the name of the
first record from Table2 to go into the first corresponding record
in Table1 and the 2nd record from Table2 to go into the 2nd corresponding record in Table1
Here's the CREATE TABLE and INSERT statements to populate the tables.
create table Table1
( SeqNo varchar(10)
, AcctNo varchar(10)
, WorkDate datetime
, Name varchar(50)
)
create table Table2
( SeqNo varchar(10)
, AcctNo varchar(10)
, WorkDate datetime
, Name varchar(50)
)
Insert table1 values ( '000014' , '6543210987654321' , '1/1/2016' , '' )
Insert table1 values ( '000012' , '1234567890123456' , '1/1/2016' , '' )
Insert table1 values ( '000013' , '1234567890123456' , '1/1/2016' , '' )
Insert table2 values ( '000014' , '6543210987654321' , '1/1/2016' , 'Steve Smith' )
Insert table2 values ( '000002' , '1234567890123456' , '1/1/2016' , 'John Doe' )
Insert table2 values ( '000003' , '1234567890123456' , '1/1/2016' , 'Jane Doe' )
Thank you in advance.
January 27, 2016 at 1:14 pm
There's probably a more efficient way of doing it than this, but here's one potential solution with your sample data:
WITH numbered_table1 AS
(SELECT rn=ROW_NUMBER() OVER (PARTITION BY AcctNo, WorkDate ORDER BY SeqNo ASC),
*
FROM table1
),
numbered_table2 AS
(SELECT rn=ROW_NUMBER() OVER (PARTITION BY AcctNo, WorkDate ORDER BY SeqNo ASC),
*
FROM table2
)
UPDATE t1
SET t1.name=t2.name
FROM numbered_table1 t1
INNER JOIN numbered_table2 t2 ON t1.AcctNo=t2.AcctNo AND
t1.WorkDate=t2.WorkDate AND
t1.rn=t2.rn;
Cheers!
EDIT: I forgot to mention that, as Alan pointed out, your current sample data setup will not run, since the account numbers are longer than 10 characters. I just tweaked the types to be long enough, but Alan's points stand.
January 27, 2016 at 1:18 pm
I don't fully get what you are trying to do here. Can you post some sample data that shows what the desired output looks like?
In the meantime, here's a few things to take note of:
1. SeqNo should be an int, not a varchar(10)
Note: there is no such thing as a varchar value that is 1 less/greater than another varchar
2. Ditto for AccountNo; also your sample data includes AcctNo's longer than 10
What defines "first" or "second" "corresponding record"? What's the ORDER BY?
Here's what better DDL would look like in case someone else wants to take a crack at this:
IF OBJECT_ID('tempdb..#Table1') IS NOT NULL DROP TABLE #Table1;
IF OBJECT_ID('tempdb..#Table2') IS NOT NULL DROP TABLE #Table2;
create table #Table1
( SeqNo int NOT NULL primary key clustered
, AcctNo bigint NOT NULL
, WorkDate datetime NOT NULL
, Name varchar(50) NOT NULL
)
create table #Table2
( SeqNo int NOT NULL primary key clustered
, AcctNo bigint NOT NULL
, WorkDate datetime NOT NULL
, Name varchar(50) NOT NULL
)
Insert #table1 values
( '000014' , '6543210987654321' , '1/1/2016' , '' ),
( '000012' , '1234567890123456' , '1/1/2016' , '' ),
( '000013' , '1234567890123456' , '1/1/2016' , '' );
Insert #table2 values
( '000014' , '6543210987654321' , '1/1/2016' , 'Steve Smith' ),
( '000002' , '1234567890123456' , '1/1/2016' , 'John Doe' ),
( '000003' , '1234567890123456' , '1/1/2016' , 'Jane Doe' );
SELECT
SeqNo,
AcctNo,
WorkDate,
Name
FROM #Table1;
SELECT
SeqNo,
AcctNo,
WorkDate,
Name
FROM #Table2;
-- Itzik Ben-Gan 2001
January 27, 2016 at 1:22 pm
My apologies on the AcctNo column, it should have been VARCHAR(50), that was a typo. The source data for both seqno and acctno is character data and not intergers.
January 27, 2016 at 1:24 pm
Jacob,
That worked nicely, thank you very much.
Mark
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply