Need to update a column in a table from referencing from other table without hardcoding

I am currently working on a query which needs to update the column posting_ID in transaction table based on the information from Contract table for each client.

For example, the transaction table have 3 rows of data with different Posting_IDs. But these IDs needs to be updated by referencing into Contract table which will have Contract_NM column. Below is the picture which describes which Contract_NMs should go into what Posting_ID. I can easily do this by writing a case statement and hardcoding the values, but our requirement is not to hardcode it.

Create Table #Transaction 
(ID int, Posting_ID Varchar(10), Contract_NM varchar(25), Client_NM varchar(25))

Insert Into #Transaction
Values 
(1, '94260','LC01','ACA'),(2, '94260','LC02','ACA'),(3, '12458','LC03','ACA'),(4, 
'92250','LC04','ACA'),(5, '92252','LC05','ACA')

 Select * from #Transaction

 Create Table #Contract
 (Posting_ID Varchar(10), Contract_NM varchar(25), Client_NM varchar(25))

  Insert Into #Contract
  Values
  ('94265',N'LC01,LC02','ACA'),('12422',N'LC03','ACA'),('94260',N'LC04,LC05','ACA')

   Select * from #Contract

Now, based on the data provided, I need to update Posting_ID column in Transaction table by referencing the Contract_NM column from the Contract table.

Can someone help with the solution here?

Solution

DATA

IF(OBJECT_ID('tempdb..#Transaction') is not null)
    DROP TABLE #Transaction;

Create Table #Transaction (ID int, Posting_ID Varchar(10), Contract_NM varchar(25), Client_NM varchar(25));

Insert Into #Transaction
Values 
    (1, '94260','LC01','ACA'),
    (2, '94260','LC02','ACA'),
    (3, '12458','LC03','ACA'),
    (4, '92250','LC04','ACA'),
    (5, '92252','LC05','ACA');

IF(OBJECT_ID('tempdb..#Contract') is not null)
    DROP TABLE #Contract;

Create Table #Contract (Posting_ID Varchar(10), Contract_NM varchar(25), Client_NM varchar(25));

Insert Into #Contract
Values
    ('94265',N'LC01,LC02','ACA'),
    ('12422',N'LC03','ACA'),
    ('94260',N'LC04,LC05','ACA');

Query

WITH contract_split(Posting_ID, Contract_NM, Contract_NM_list) AS
(
    SELECT
        Posting_ID,
        CAST(LEFT(Contract_NM, CHARINDEX(',', Contract_NM + ',') - 1) as nvarchar),
        STUFF(Contract_NM, 1, CHARINDEX(',', Contract_NM + ','), '')
    FROM #Contract

    UNION ALL

    SELECT
        Posting_ID,
        CAST(LEFT(Contract_NM_list, CHARINDEX(',', Contract_NM_list + ',') - 1) as nvarchar),
        STUFF(Contract_NM_list, 1, CHARINDEX(',', Contract_NM_list + ','), '')
    FROM contract_split
    WHERE
        Contract_NM_list > ''
)
UPDATE t
    SET t.Posting_ID = cs.Posting_ID
FROM contract_split cs
JOIN #Transaction t ON t.Contract_NM = cs.Contract_NM

Details The CTE takes the Contract_NM and adds a comma and then selects everything to the first comma it finds. The stuff then replaces the first Contract_NM up to the comma with nothing and stores the rest for the next recursive call. Note: you can add trim if your data has whitespace. When the CTE is done, it will contain all the values in rows. Add OPTION (maxrecursion 0) if you are running this on many rows.

The update joins the CTE to the #Transaction table for updating. I just replaced the #Transaction Posting_ID with the one from the #Contract table. Not sure if that was your intent or not.

Transaction Table Results

pre-update	Posting_ID (after update)	Contract_NM
94260	94265	LC01
94260	94265	LC02
12458	12422	LC03
92250	94260	LC04
92252	94260	LC05