使用子查询 SQL PL/SQL 进行更新

发布于 2024-09-07 15:06:41 字数 506 浏览 3 评论 0原文

我正在尝试更新表，但我的语法无法正常工作。我将临时表中的新元素添加到元素表中，并且想要更新metadata_attribute 中的column_name 和metadata_table 表中的table_name。这就是我所拥有的：

UPDATE METADATA_ATTRIBUTE
   SET C.ELEMENT_ID = (SELECT ELEMENT_ID
                         FROM ELEMENT A , TEMP B 
                        WHERE A.ELEMENT_NAME = B.WMIS_COLUMN_NAME)
 FROM ELEMENT A, TEMP B, METADATA_ATTRIBUTE C,  METADATA_TABLE D 
WHERE C.COLUMN_NAME = B.WMIS_COLUMN_NAME
  AND D.TABLE_NAME = B.WMIS_TABLE_NAME
  AND ELEMENT_ID IS NULL;

原文

I am trying to update a table, but I can't get my syntax to work. I added new elements from a temp table to my element table and I want to update column_name in metadata_attribute and table_name in metadata_table table. This is what I have:

UPDATE METADATA_ATTRIBUTE
   SET C.ELEMENT_ID = (SELECT ELEMENT_ID
                         FROM ELEMENT A , TEMP B 
                        WHERE A.ELEMENT_NAME = B.WMIS_COLUMN_NAME)
 FROM ELEMENT A, TEMP B, METADATA_ATTRIBUTE C,  METADATA_TABLE D 
WHERE C.COLUMN_NAME = B.WMIS_COLUMN_NAME
  AND D.TABLE_NAME = B.WMIS_TABLE_NAME
  AND ELEMENT_ID IS NULL;

分享到QQ

分享到微博

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

发布评论

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

舟遥客 2024-09-14 15:06:41

从单个更新语句更新两个表在 Oracle 中是不可能的。

您需要编写一个PL/SQL 块（过程或触发器）来解决这个问题。

如果您需要更多帮助，请详细说明您的问题。

回复收藏 0 原文

风铃鹿 2024-09-14 15:06:41

当你尝试这个时会发生什么？

update a set a.element_id = b.element_id 
from element a, temp b, metadata_attribute c, metadata_table d 
where c.column_name = b.wmis_column_name and 
d.table_name = b.wmis_table_name and 
element_id is null;

What happens when you try this?

update a set a.element_id = b.element_id 
from element a, temp b, metadata_attribute c, metadata_table d 
where c.column_name = b.wmis_column_name and 
d.table_name = b.wmis_table_name and 
element_id is null;

回复收藏 0 原文

累赘 2024-09-14 15:06:41

试试这个：


UPDATE METADATA_ATTRIBUTE m
SET m.ELEMENT_ID =    /* changed C to m */
   (SELECT ELEMENT_ID /* this query must not return multiple rows! */
      FROM ELEMENT A , TEMP B  
      WHERE A.ELEMENT_NAME = B.WMIS_COLUMN_NAME
   )
where m.rowid in /* I added this line */ 
   (select C.rowid
      FROM ELEMENT A, TEMP B, METADATA_ATTRIBUTE C,  METADATA_TABLE D  
      WHERE C.COLUMN_NAME = B.WMIS_COLUMN_NAME 
       AND D.TABLE_NAME = B.WMIS_TABLE_NAME 
       AND ELEMENT_ID IS NULL
       /* the ELEMENT table is not explicitly joined to any table - Carteansan Product!!!*/
    );

这将更新 METADATA_ATTRIBUTE 表中的行。

try this:


UPDATE METADATA_ATTRIBUTE m
SET m.ELEMENT_ID =    /* changed C to m */
   (SELECT ELEMENT_ID /* this query must not return multiple rows! */
      FROM ELEMENT A , TEMP B  
      WHERE A.ELEMENT_NAME = B.WMIS_COLUMN_NAME
   )
where m.rowid in /* I added this line */ 
   (select C.rowid
      FROM ELEMENT A, TEMP B, METADATA_ATTRIBUTE C,  METADATA_TABLE D  
      WHERE C.COLUMN_NAME = B.WMIS_COLUMN_NAME 
       AND D.TABLE_NAME = B.WMIS_TABLE_NAME 
       AND ELEMENT_ID IS NULL
       /* the ELEMENT table is not explicitly joined to any table - Carteansan Product!!!*/
    );

This will update rows in the METADATA_ATTRIBUTE table.

回复收藏 0 原文

~没有更多了~