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

CodePudding user response：

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))

CodePudding user response：

Thanks for your answer :)

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))

i think the second line should write DATE instead of OLD_DATE

AND i dont understand why we used min? Can you explain pls?