Oracle 更新连接语法不起作用

发布于 2024-10-31 16:17:55 字数 435 浏览 0 评论 0原文

这是我在 MS Access 中的工作查询...

UPDATE Caxnode AS A INNER JOIN Caxnode AS B ON A.node_alias = B.node_alias 
SET A.partition_Type = 'LDOM', A.node_mode = 'LOGICAL', A.host_id = b.host_id, A.num_of_proc = b.num_of_proc 
WHERE (((A.node_mode)='virtual' Or (A.node_mode)='regular') AND ((B.partition_Type)='LDOM'));

这在 Oracle 中不起作用，我在 google 上搜索并读到更新不适用于 Oracle 中的内部联接。

有人可以帮我将此查询翻译为在 Oracle 上工作吗？

原文

This is my working query in MS Access...

UPDATE Caxnode AS A INNER JOIN Caxnode AS B ON A.node_alias = B.node_alias 
SET A.partition_Type = 'LDOM', A.node_mode = 'LOGICAL', A.host_id = b.host_id, A.num_of_proc = b.num_of_proc 
WHERE (((A.node_mode)='virtual' Or (A.node_mode)='regular') AND ((B.partition_Type)='LDOM'));

This doesn't work in Oracle, I googled and read that update doesn't work with inner join in Oracle..

Can someone please help me translate this query to work on Oracle?

分享到QQ

分享到微博

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

发布评论

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

暗藏城府 2024-11-07 16:17:55

JOIN in Update 是 MS 对 UPDATE 的专有扩展。通过谷歌搜索Oracle更新语法，我相信你可以将其写为：

UPDATE Caxnode AS A
SET (partition_Type, node_mode, host_id, num_of_proc)
= (select 'LDOM', 'LOGICAL', host_id, num_of_proc from Caxnode B where A.node_alias = B.node_alias and B.partition_Type='LDOM')
WHERE A.node_mode='virtual' Or A.node_mode='regular'

此语法确实确保如果 B 端有多行，您将收到错误，而在 Access/SQL Server 中，它会默默地随机选择其中一行。

JOIN in Update is a MS proprietary extension to UPDATE. From Googling Oracle Update Syntax, I believe you can write it as:

UPDATE Caxnode AS A
SET (partition_Type, node_mode, host_id, num_of_proc)
= (select 'LDOM', 'LOGICAL', host_id, num_of_proc from Caxnode B where A.node_alias = B.node_alias and B.partition_Type='LDOM')
WHERE A.node_mode='virtual' Or A.node_mode='regular'

This syntax does ensure that if there are multiple rows on the B side, you'll get an error, whereas in Access/SQL Server, it will silently just pick one of the rows at random.

回复收藏 0 原文

~没有更多了~