Update between 3 tables Oracle Sql

Question

i got one question.

I got 3 tables as Table1, Table2, Table3.

1. Table1 --> Column 1 (PLATE), Column 2 (DATE)
2. Table2 --> Column 1 (PLATE), Column 2 (BRAND)
3. Table3 --> Column 1 (BRAND), Column 2 (DATE)

I want to fill the DATE column of Table1 with the information in the DATE column of Table3

We can join Table1 (PLATE) and Table2 (PLATE), Table2(BRAND) and Table3 (BRAND)

I tried that but gave an error (cannot modify a column which maps to a non key-preserved table)

UPDATE
    (
        SELECT TABLE1.DATE AS OLD_DATE,
               TABLE3.DATE AS NEW_DATE
          FROM TABLE1 
          JOIN TABLE2 ON TABLE1.PLATE = TABLE2.PLATE
          JOIN TABLE3 ON TABLE3.BRAND=TABLE2.BRAND
    ) TABLES
SET TABLES.OLD_DATE = TABLES.NEW_DATE
  ;

How can i do this update?

Thanks for helps

Answer 1

Using MERGE with MATCHED option would be a good choice for your case such as

MERGE INTO table1 t1 
USING (SELECT t3."date", t2.plate
         FROM table1 t1
         JOIN table2 t2 ON t1.plate = t2.plate
         JOIN table3 t3 ON t3.brand = t2.brand) tt
          ON ( t1.plate = tt.plate )
 WHEN MATCHED THEN UPDATE SET t1."date" = tt."date"

date is a reserved keyword and cannot be used as a column name unless double-quoted. So, that's quoted. Btw, quoted identifiers are case-sensitive, I prefered "date" (not "DATE") to be the column of the table

Answer 2

This error message indicates that a Plate might map to different Brands or Brands might map to different Dates. Therefore the inline view is not updateable because it's not a key preserved table.

Try to set the value one-by-one:

UPDATE TABLE1 t1
   SET t1.OLD_DATE =
       (SELECT MIN(t3.DATE) AS NEW_DATE
          FROM TABLE2 t2
          JOIN TABLE3 t3 ON t2.BRAND = t3.BRAND
         WHERE t2.PLATE = t1.PLATE)
 WHERE EXISTS((SELECT t3.DATE AS NEW_DATE
                FROM TABLE2 t2
                JOIN TABLE3 t3 ON t2.BRAND = t3.BRAND
               WHERE t2.PLATE = t1.PLATE))