在3张表Oracle SQL之间进行更新

发布于 2025-01-24 19:07:32 字数 963 浏览 3 评论 0 原文

我有一个问题。

我得到了3个表作为 table1 ， table2 ， table3 。

table1 - ＆gt;第1列（ plate ），第2列（ date ）
table2 - ＆gt;第1列（ plate ），第2列（ brand ）
table3 - ＆gt;第1列（ brand ），第2列（ date ）

我想用日期 table1 用 table3 的列中的信息

我们可以加入 table1（plate） and table2（plate），<代码> table2（brand）和 table3（brand）

我尝试过但给出了一个错误（无法修改列映射到非密钥保存表）

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
  ;

我该如何进行此更新？

感谢您的帮助

原文

i got one question.

I got 3 tables as Table1, Table2, Table3.

Table1 --> Column 1 (PLATE), Column 2 (DATE)
Table2 --> Column 1 (PLATE), Column 2 (BRAND)
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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

疯到世界奔溃 2025-01-31 19:07:32

情况是一个不错的选择

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"

使用 Merge 与 匹配 选项对您的代码>日期是保留的关键字，除非双引用，否则不能用作列名。因此，引用了。顺便说一句，引用的标识符对案例敏感，我更喜欢“ date” （不是“ date” ）是表的列

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

回复收藏 0 原文

人│生佛魔见 2025-01-31 19:07:32

此错误消息表明，板可能会映射到不同的品牌或品牌可能映射到不同的日期。因此，内联视图无法更新，因为它不是。

尝试一对一设置值：

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

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

回复收藏 0 原文

~没有更多了~