How to compare two tables and if the values match, update one of the tables with the values from a third table?

I have 3 tables, the MAIN_TABLE, the SUB_TABLE and the ID_TABLE.

I need to compare the CODE in the MAIN_TABLE with the CODE in the SUB_TABLE, and if they match, search for the SUB_ID in the ID_TABLE and update the ID in the MAIN_TABLE with that ID.

In the example shown below, the query should update the MAIN_TABLE with the ID = 2071.

MAIN_TABLE:

CODE	ID
0290380007800	994526

SUB_TABLE:

CODE	SUB_ID
029038078	106603

ID_TABLE:

ID	SUB_ID
2071	106603

To match the code from the MAIN_TABLE with the code from the SUB_TABLE, I need to select it like this:

SELECT 
    SUBSTRING(CODE, 1, 6) + SUBSTRING(CODE, 9, 3) 
FROM 
    MAIN_TABLE

How can I achieve this?

Here’s the dbfiddle with more data in each table: https://dbfiddle.uk/6H_mnPDR?hide=28

Answer

Just join your tables together as part of an update statement. Note this gives you duplicates, but then you already had duplicate IDs so I guess thats expected (although unusual).

UPDATE mt SET
    id = it.id
FROM MAIN_TABLE mt
INNER JOIN SUB_TABLE st ON st.code = SUBSTRING(mt.CODE, 1, 6) + SUBSTRING(mt.CODE, 9, 3) 
INNER JOIN ID_TABLE it ON it.SUB_ID = st.SUB_ID;