Update Between Two Tables

I created the question in "Rich Text Mode", so might need to view in that to get the proper layout for the tables.

I have two tables.

Table1

UserID	TicketNumber	CaseID	Status
1	INC1234567	NULL	NULL
2	WS12345678	NULL	NULL
3	HIG1234567	NULL	NULL

Table2

UserID	INC	ABC	WS	HIG	CaseID	Status
1	Yes	Yes	NULL	NULL	Case123	Exception
2	NULL	Yes	Yes	Yes	Case981	Granted
3	NULL	Yes	NULL	NULL	Case871	Not Granted

I want to update the columns Table1.CaseID, Table1.Status, based on the values in Table2.CaseID, Table2.Status. Not overly difficult to simply join on the UserID between the two tables to accomplish this.

Where it gets difficult for me, is that we only update the Table1 columns if certain conditions are met.

UserIDs match (given)
The alpha part of Table1.TicketNumber has a "Yes" in the corresponding column in Table2. So using UserID = 1 as an example, We would update Table.CaseID = 'Case123" and Status = 'Exception' because the UserIDs match, and under Table2.INC = 'Yes'. We would update Table1.CaseID = 'Case981' and Status = 'Granted' because UserIDs match and Table1.TicketNumber's alpha characters = 'WS' and Table2.WS = 'Yes'. We would not update any column for Table1.UserID = 3 because the Table1.TicketNumber alpha characters = 'HIG', and Table2.HIG has a NULL value.

I am able to get the basic update statement:

UPDATE 
    A
SET
    A.CaseID = B.CaseID, 
    A.Status = B.Status
FROM 
    Table1 A
    INNER JOIN Table2 B ON A.UserID = B.UserID
WHERE
    A.CaseID IS NULL
    AND A.Status = 'New'

It is this second part that is given me a lot of trouble, comparing the Table1.TicketNumber to the various columns in Table2. My first thought was using a Cursor, but I think this would be very inefficient. My next thought would be to create a function for each "Type" of TicketNumber, and pass in the TicketNumber, but I have never used a function in T-SQL and not sure if this would work. Something like this:

UPDATE 
    A
SET
    A.CaseID = B.CaseID, 
    A.Status = B.Status
FROM 
    Table1 A
    INNER JOIN Table2 B ON A.UserID = B.UserID
WHERE
    A.CaseID IS NULL
    AND A.Status IS NULL
        AND 
            (dbo.IsTicketINC(A.TicketNumber) = 'Y' AND B.INC = 'Yes')
            OR (dbo.IsTicketABC(A.TicketNumber) = 'Y' AND B.ABC = 'Yes')
            OR dbo.IsTicketWS(A.TicketNumber) = 'Y' AND B.WS = 'Yes')
            OR ........

I am not sure if this would be valid and work either, but it was my other thought. I know I can do this with a cursor in T-SQL, but I think there should be a way to accomplish this with a single Update Statement, using some Case Statements and I am at a loss for this part unless I go the route of functions.

Solution

How about using the LIKE operator to match the prefix as follows:

WHERE
    A.CaseID IS NULL
    AND A.Status IS NULL
    AND (
       (B.INC = 'Yes' AND A.TicketNumber LIKE 'INC[0-9]%')
       OR (B.ABC = 'Yes' AND A.TicketNumber LIKE 'ABC[0-9]%')
       OR (B.WS = 'Yes' AND A.TicketNumber LIKE 'WS[0-9]%')
       OR ...
    )

The LIKE operator allow you to match just the ticket number prefix. The [0-9] part ensures that the matched prefix is immediately followed by a numeric, so that a 'WS' prefix is not mistakenly matched to a 'WSX123' ticket number.

Perhaps clearer variation would be to move the match condition to the ON condition.

    INNER JOIN Table2 B
        ON A.UserID = B.UserID
        AND (
           (B.INC = 'Yes' AND A.TicketNumber LIKE 'INC[0-9]%')
           OR (B.ABC = 'Yes' AND A.TicketNumber LIKE 'ABC[0-9]%')
           OR (B.WS = 'Yes' AND A.TicketNumber LIKE 'WS[0-9]%')
           OR ...
        )

This is functionally identical when applied to an inner join. It is just a question of style and readability.

Results:

UserID	TicketNumber	CaseID	Status
1	INC1234567	Case123	Exception
2	WS12345678	Case981	Granted
3	HIG1234567	null	null

Side note: Make sure that you wrap your chain of OR conditions in parenthesis to ensure intended operator evaluation order. In your original code, the A.CaseID IS NULL AND A.Status IS NULL conditions would only be enforced for the INC case, but not the other ABC and WS cases. (Technically, the parentheses around the AND conditions are not needed, but when dealing with a complex nested logical expression, sometimes more parenthesis are better.)

See this db<>fiddle for a demo.