开发者

Oracle update with join syntax not working

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 joi开发者_运维知识库n in Oracle..

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


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

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜