Sql-server – Update values from one table to another where key values are not unique

querysql-server-2012update

I've been trying to figure this out for hours and can't seem to get it. If it can be done, any help would be greatly appreciated! I hope I'm clear with what I'm asking 🙂

Consider the following:

Table A consists of lastname (null), firstname(null), merchant, amount, transdate, reference; with only unique field value being reference. The merchant, amounts and transdate can have similar duplicated values.

example:

Row 1:  {Null},{Null},"BLANK-ROSTER NUM","34.30","2018-01-01","12345" 
Row 2:  {Null},{Null},"BLANK-ROSTER NUM","34.30","2018-01-01","77777" 
Row 3:  {Null},{Null},"BLANK-ROSTER NUM","16.07","2018-12-24","99999" 
Row 4:  {Null},{Null},"BLANK-ROSTER NUM","16.07","2018-12-24","44444" 
Row 5:  {Null},{Null},"BLANK-ROSTER NUM","34.30","2018-01-01","66666"

The other table has the lastname and firstname values I want to assign to each line, similar merchant,amount and transdate values throughout, but no reference field.

example:

Row 1:  SMITH,JOHN,"BLANK-ROSTER NUM","34.30","2018-01-01"
Row 2:  JONES,JAMES,"BLANK-ROSTER NUM","34.30","2018-01-01"
Row 3:  MILLER,STEVE,"BLANK-ROSTER NUM","16.07","2018-12-24"
Row 4:  WILSON,JACK,"BLANK-ROSTER NUM","34.30","2018-01-01"
Row 5:  DAVIS,BEN,"BLANK-ROSTER NUM","16.07","2018-12-24"

Is there a way to get names updated into the similar rows in TABLE A? It doesn't matter which goes to which so long as one name doesn't populate all similar rows in TABLE A (which is what happens to me). Any row with same merchant, amount, and date can update to any row in Table A that has same merchant, amount, and date. Avoiding the duplication of data is my issue.

Is it possible to get in TABLE A the names to distinctly assign to a row?

UPDATED TABLE A after assigning names from rows with same merchant, amount, and date distinctly:

Row 1:  "SMITH", "JOHN","BLANK-ROSTER NUM","34.30","2018-01-01","12345" 
Row 2:  "JONES","JAMES","BLANK-ROSTER NUM","34.30","2018-01-01","77777" 
Row 3:  "MILLER","STEVE","BLANK-ROSTER NUM","16.07","2018-12-24","99999" 
Row 4:  "DAVIS","BEN","BLANK-ROSTER NUM","16.07","2018-12-24","44444" 
Row 5:  "WILSON","JACK","BLANK-ROSTER NUM","34.30","2018-01-01","66666"

//////////Edited to include Table Def using SQL Server 2012

Table A:

 INSERT INTO [Recon2018].[dbo].[RECONReport]
       ([Employee - Last]
       ,[Employee - First]
       ,[MI]
       ,[Posted Amount]
       ,[Merchant]
       ,[Reference]
       ,[transdate])
 VALUES
       (<Employee - Last, nvarchar(50),>
       ,<Employee - First, nvarchar(255),>
       ,<MI, nvarchar(255),>
       ,<Posted Amount, decimal(18,2),>
       ,<Merchant, nvarchar(50),>
       ,<Reference, nvarchar(50),>
       ,<transdate, datetime2(0),>)

TABLE B:

 INSERT INTO [Recon2018].[dbo].[Summary_Final]
       ([Employee Last Name]
       ,[Employee First Name]
       ,[Employee Middle Initial]
       ,[Merchant]
       ,[CLIN Rate (Fee)]
       ,[Fee Transaction Date])
 VALUES
       (<Employee Last Name, nvarchar(max),>
       ,<Employee First Name, nvarchar(max),>
       ,<Employee Middle Initial, nvarchar(255),>
       ,<Merchant, nvarchar(max),>
       ,<CLIN Rate (Fee), float,>
       ,<Fee Transaction Date, datetime2(0),>)

Best Answer

This solution seems to work against your sample data.

--Demo setup
Declare @TableA table
    (FirstName varchar(6), LastName varchar(6), Merchant varchar(16), Amount decimal(11,2), TransDate varchar(10), Reference int)
;

INSERT INTO @TableA
    (FirstName, LastName, Merchant, Amount, TransDate, Reference)
VALUES
    ('{Null}', '{Null}', 'BLANK-ROSTER NUM', 34.30, '2018-01-01', 12345),
    ('{Null}', '{Null}', 'BLANK-ROSTER NUM', 34.30, '2018-01-01', 77777),
    ('{Null}', '{Null}', 'BLANK-ROSTER NUM', 16.07, '2018-12-24', 99999),
    ('{Null}', '{Null}', 'BLANK-ROSTER NUM', 16.07, '2018-12-24', 44444),
    ('{Null}', '{Null}', 'BLANK-ROSTER NUM', 34.30, '2018-01-01', 66666)
;

Declare @TableB table
    (FirstName varchar(6), LastName varchar(6), Merchant varchar(16), Amount decimal(11,2), TransDate varchar(10))
;
INSERT INTO @TableB
    (FirstName, LastName, Merchant, Amount, TransDate)
VALUES
    ('SMITH', 'JOHN', 'BLANK-ROSTER NUM', 34.30, '2018-01-01'),
    ('JONES', 'JAMES', 'BLANK-ROSTER NUM', 34.30, '2018-01-01'),
    ('MILLER', 'STEVE', 'BLANK-ROSTER NUM', 16.07, '2018-12-24'),
    ('WILSON', 'JACK', 'BLANK-ROSTER NUM', 34.30, '2018-01-01'),
    ('DAVIS', 'BEN', 'BLANK-ROSTER NUM', 16.07, '2018-12-24')
;

--The solution
;

--Common table expressions to put a row number on each row
--partition by merchant, amount and transdata
WITH TableARowNumbers
AS (
    SELECT *
        ,ROW_NUMBER() OVER (
            PARTITION BY Merchant
            ,Amount
            ,TransDate ORDER BY Merchant
                ,Amount
                ,TransDate
            ) AS rn
    FROM @TableA
    )
    ,TableBRowNumbers
AS (
    SELECT *
        ,ROW_NUMBER() OVER (
            PARTITION BY Merchant
            ,Amount
            ,TransDate ORDER BY Merchant
                ,Amount
                ,TransDate
            ) AS rn
    FROM @TableB
    )

--Update TableA joining on Merchant, Amount, TransDate and rn
UPDATE ta
SET ta.FirstName = tb.FirstName
    ,ta.LastName = tb.LastName
FROM TableARowNumbers ta
JOIN TableBRowNumbers tb
    ON tb.Merchant = ta.Merchant
        AND tb.Amount = ta.Amount
        AND tb.TransDate = ta.TransDate
        AND tb.rn = ta.rn

SELECT *
FROM @TableA

| FirstName | LastName | Merchant         | Amount | TransDate  | Reference |
|-----------|----------|------------------|--------|------------|-----------|
| SMITH     | JOHN     | BLANK-ROSTER NUM | 34.30  | 2018-01-01 | 12345     |
| JONES     | JAMES    | BLANK-ROSTER NUM | 34.30  | 2018-01-01 | 77777     |
| MILLER    | STEVE    | BLANK-ROSTER NUM | 16.07  | 2018-12-24 | 99999     |
| DAVIS     | BEN      | BLANK-ROSTER NUM | 16.07  | 2018-12-24 | 44444     |
| WILSON    | JACK     | BLANK-ROSTER NUM | 34.30  | 2018-01-01 | 66666     |
Related Question