update query with select from same table help!!!

[nkshirsa]

I have a table caxnode which has fields node_alias, node_mode, node_id, etc..

In some cases, node_alias is marked as "regular" or 'logical' for different node_id's. I want to change the entries for which this same node_alias is also marked ''logical' somewhere else in the same table... i.e. change the "regular/virtual' to "logical" if the same entry is marked 'logical' in the table and is an LDOM partition .. and also update the id to become the id of the node that is marked 'logical'

This is my working query to return the results that I am interested in changing..

select Node_ID, Node_type, Num_of_proc, Node_Alias, Host_ID, Node_mode, Partition_Type
from CAXNODE nd
where (Node_mode = 'VIRTUAL' or Node_mode = 'REGULAR')
and Node_Alias in
(select nd2.Node_Alias
from CAXNODE nd2
where nd2.Node_mode = 'LOGICAL' and Partition_Type = 'LDOM'
) ;

Now I want to change the values to set Node_mode to 'LOGICAL'; Partition_Type to 'LDOM', Host_ID to (nd2.Host_ID); Num_of_proc to (nd2.Num_of_proc);

How can I do this in one update statement?

for eg..

my table is as follows

id node_alias host_id node_mode partition_type num_procs

1 abc abc virtual null 2
2 abc xyz logical LDOM 4
3 def def virtual null 2
4 def ppp logical LDOM 8
5 abc abc regular null 3

So those that are ldoms are marked ldom in partition_type, those that are not recognised as ldoms are marked null in partition_type. LDOM's are logical in node_mode, others are either virtual or regular in node_mode.

Now since there are some old entries that are marked regular/virtual and NULL but are actually LDOMs and have LDOM entries as well, I need to go through the table and mark those as LDOM's which have same node_alias marked as LDOM later on.

In the table above, abc is marked as LDOM in row 2, so row 1 and 5 should reflect that. the host id should change to host_id in row 2. Same with def.. host_id for def should change to host_id in row 4..

The table should look like this

id alias host_id node_type num_procs
1 abc xyz LDOM 4
2 abc xyz LDOM 4
3 def ppp LDOM 8
4 def ppp LDOM 8
5 abc xyz LDOM 4


Can someone tell me an update query for this?

If possible, please help me with syntax that might be compatible in oracle as well.. thanks in advance for your help!

Trying this on access but its giving me a syntax error.. any idea what the problem is? logically it seems the right query..

UPDATE nd
SET
Host_ID = nd2.Host_ID,
Num_of_proc = nd2.Num_of_proc
from CAXNODE nd
where (Node_mode = 'VIRTUAL' or Node_mode = 'REGULAR')
and Node_Alias in
(select nd2.Node_Alias
from CAXNODE nd2
where nd2.Node_mode = 'LOGICAL' and Partition_Type = 'LDOM'
) ;

 

[PHV]

A starting point:

UPDATE CAXNODE nd SET
 Host_ID=(SELECT Host_ID FROM CAXNODE WHERE Node_Alias=nd.Node_Alias AND Node_mode='LOGICAL' AND Partition_Type='LDOM')
,Num_of_proc=(SELECT Num_of_proc FROM CAXNODE WHERE Node_Alias=nd.Node_Alias AND Node_mode='LOGICAL' AND Partition_Type='LDOM')
,Partition_Type='LDOM'
WHERE Node_mode IN ('VIRTUAL','REGULAR')
AND Node_Alias IN (SELECT Node_Alias FROM CAXNODE WHERE Node_mode='LOGICAL' AND Partition_Type='LDOM')

Due to some strange behaviour of JetSQL you may have to play with DLookUp instead of subquery in the SET clause.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886