CODEWITHSUNDEEP
sqldb2
user3072470
user3072470

Reputation: 131

Update multiple rows by joining many tables

I have 3 tables, i'm trying to find a way to update multiple rows using other tables data. Update only null or empty "info" column in table1 with "data" column in the Table2 by checking if the id in table1 is linked to the idTab1 in table3 and the id in table2 is linking to idTab2 in Table3

enter code here
Table1
id, info
 --------
 1, null
 2, info2
 3, null
 
 Table2
 id, data
  ---------
  1, info1
  2, info2
  3, info4

 Table3
 idTab1, idTab2
 ------
 1, 1
 2, 2
 3, 3

The desired result is :

Table1
id, info
--------
 1, info1
 2, info2
 3, info3

Thank you in advance.

Upvotes: 1

Views: 91

Answers (2)

Sergey
Sergey

Reputation: 5225

    CREATE TABLE TABLE1
    (
       ID TINYINT NOT NULL,
       INFO VARCHAR(100)
    )
    INSERT TABLE1(ID,INFO)
       SELECT 1,NULL 
        UNION ALL
       SELECT 2,'INFO2'
        UNION ALL
      SELECT 3,NULL

    CREATE TABLE TABLE2
    (
      ID TINYINT NOT NULL,
      DATA VARCHAR(100)NOT NULL
    )

    INSERT TABLE2(ID,DATA)

      SELECT 1,'INFO1'
        UNION ALL
      SELECT 2,'INFO2'
        UNION ALL
      SELECT 3,'INFO4'

    CREATE TABLE TABLE3
    (
      idTab1 TINYINT,
      idTab2 TINYINT 
    )

    INSERT TABLE3(idTab1,idTab2)
      SELECT 1,1
        UNION ALL
      SELECT 2,2
        UNION ALL
      SELECT 3,3     

    MERGE INTO TABLE1 AS DEST
    USING
    (
    SELECT T1.ID,T2.DATA
    FROM TABLE1 AS T1
    JOIN TABLE3 AS T3 ON T1.ID=T3.idTab1
    JOIN TABLE2 AS T2 ON T3.idTab2=T2.ID 
    )AS SRC ON DEST.ID=SRC.ID
    WHEN MATCHED AND (DEST.INFO IS NULL OR DEST.INFO='')THEN UPDATE SET
      DEST.INFO=SRC.DATA;

    SELECT *FROM TABLE1;

    --DROP TABLE TABLE1;
    --DROP TABLE TABLE2;
    --DROP TABLE TABLE3;

Upvotes: 0

forpas
forpas

Reputation: 164174

You can join the 3 tables in the UPDATE statement with this syntax:

UPDATE Table1
SET Table1.info = t2.data
FROM Table3 t3 INNER JOIN Table2 t2
ON t2.id = t3.idTab2
WHERE t3.idTab1 = Table1.id AND Table1.info IS NULL

See the demo.
Results:

> ID |  INFO
> -: | ----:
>  1 | info1
>  2 | info2
>  3 | info4

Upvotes: 1

Related Questions