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

Question

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

This may help: https://stackoverflow.com/questions/4602083/sql-how-to-compare-two-tables-for-same-data-content — Dorrin Samadian, Oct 18 '22 at 19:32

Dale K · Accepted Answer · 2022-10-19T19:29:37.993

1

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;

edited Oct 19 '22 at 19:29

answered Oct 18 '22 at 19:39

Dale K

25,246
15
42
71

1

Clean and simple, thanks! And yes, the duplicate IDs are expected. – ViNi Oct 19 '22 at 19:29

score 0 · Answer 2 · edited Oct 19 '22 at 19:16

Using a 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

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

2 Answers2