updating a table based on data in same table

[nkshirsa]

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

In some cases, the node_alias is marked as "regular" as well as 'logical' for different node_id's. I want to change the entries for which the same node_alias is marked 'regular' as well as 'logical' to logical. i.e. change the "regular" to "logical" if the same entry is marked 'logical' in the table and is an LDOM partition ..

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?

here's what i am looking for

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!
-Nikhil.

 

[mike-strawberry]

Hi nkshirsa,

This will be possible that is pretty definate, but I need a number of hours to work it out (can't give you a full guarantee that I will be able to in the timescale you require). Sorry but right now I cannot spare the time to work it out fully but if it can wait a few days I can do more on it. Perhaps my idea below might spark someonelse to answer it.

My thinking is that it will probably need a subquery to get all the data where node_alias is NOT host_id. This dataset will give you the equivalent of a look-up table to use with cases where node_id = host id. This latter set is the set to be updated.

That's all I can do for the moment.

-- Mike

 

[mike-strawberry]

Hi Nikhil

I have built a little database with a table to have the same info as you gave and created two update queries using Access design grid. Each of the two update queries has a query instead of table as source (see pic attached below). The source query for the abc update query is the SELECT query shown below. I was hoping to just paste the SQL from the update queries but when I do it refers to the subquery instead of quoting in pure SQL. Perhaps your SQL is better than mine and you will be able put them together. Anyway I applied my two queries on a copy of your original table and it successfully produced your second desired table.

You can see that I have focused on where the Host_Id is equal to the node alias since these define the records to be updated in two goes rather than three if using the node_mode.

The name of the SELECT query below I gave as qryABChost, so hopefully you can translate the UPDATE query which uses the SELECT subquery. Same applies to the def case.

SELECT CAXNODE.ID, CAXNODE.Node_ID, CAXNODE.Node_type, CAXNODE.Num_of_proc, CAXNODE.Node_Alias, CAXNODE.Host_ID, CAXNODE.Node_mode, CAXNODE.Partition_Type
FROM CAXNODE
WHERE (((CAXNODE.Host_ID)="abc"));

UPDATE qryABChost_id SET qryABChost_id.Num_of_proc = "4", qryABChost_id.Host_ID = "xyz", qryABChost_id.Node_mode = "logical", qryABChost_id.Partition_Type = "LDOM";


SELECT CAXNODE.ID, CAXNODE.Node_ID, CAXNODE.Node_type, CAXNODE.Num_of_proc, CAXNODE.Node_Alias, CAXNODE.Host_ID, CAXNODE.Node_mode, CAXNODE.Partition_Type
FROM CAXNODE
WHERE (((CAXNODE.Host_ID)="def"));

UPDATE qryDEFhost_id SET qryDEFhost_id.Num_of_proc = "8", qryDEFhost_id.Host_ID = "ppp", qryDEFhost_id.Node_mode = "logical", qryDEFhost_id.Partition_Type = "LDOM";

-- Mike