How to compare two tables and if the values match, update one of the tables with the values from a t-CodePudding

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

CodePudding user response：

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

CodePudding user response：

use Sub Query you can solve your problem .You can try below code :

update MAIN_TABLE set MAIN_TABLE.ID = Final.SUB_ID from (

select distinct ID_TABLE.SUB_ID , MAIN_TABLE.codeNew,MAIN_TABLE.Code,SUB_TABLE.Code

from (

select SUBSTRING(CODE, 1, 6) SUBSTRING(CODE, 9, 3) as CodeNEw, Code from MAIN_TABLE) as MAIN_TABLE

inner join SUB_TABLE on MAIN_TABLE.CodeNew = SUB_TABLE.Code

inner join ID_TABLE on ID_TABLE.SUB_ID = SUB_TABLE.SUB_ID ) Final where MAIN_TABLE.code =Final.Code