help with update query with select from same table

[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'
) ;

[orange]

I believe the syntax is wrong. You would NOT have a FROM clause (marked in red) in a basic UPDATE query.
You would use CAXNODE (not nd) in the UPDATE statement first line. I haven't gone thru all your requirement, but would suggest you back up your data before running any Update query.

Have you tried using a SELECT query to verify which records you are going to change/update?

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'
) ;

[nkshirsa]

Hi,

yes, there is no from clause in update in access (and oracle too i think)..

What I need is ansi sql to do this..

The select statement works.. and is as follows.. I have posted it in my initial question as well ..

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'
) ;


Since there is no from clause, I tried something like this in access..

UPDATE caxnode AS nd SET Node_mode = 'LOGICAL', Partition_Type = 'LDOM', Host_ID = nd2.Host_ID, Num_of_proc = nd2.Num_of_proc
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 nd2.Partition_Type = 'LDOM'
);

This works ok when I double click the query, it shows me the correct fields to be modified, but when i try to run the query, it asks me for the value of nd2.Host_ID as an input parameter

Then in desperation, I tried this ..

update caxnode
set caxnode.node_mode='LOGICAL', caxnode.partition_type='LDOM',
caxnode.Host_id= (select top 1 nd2.host_id
from CAXNODE nd2
where caxnode.Node_Alias=nd2.Node_Alias and nd2.Node_mode = 'LOGICAL' and nd2.Partition_Type = 'LDOM'),
caxnode.num_of_proc=(select top 1 nd2.num_of_proc from caxnode nd2 where caxnode.Node_Alias=nd2.Node_Alias and nd2.Node_mode = 'LOGICAL' and nd2.Partition_Type = 'LDOM')
where
(caxnode.Node_mode = 'VIRTUAL' or caxnode.Node_mode = 'REGULAR') and
exists (select *
from CAXNODE nd2
where caxnode.Node_Alias=nd2.Node_Alias and nd2.Node_mode = 'LOGICAL' and nd2.Partition_Type = 'LDOM');

trying to solve the "operation must use an updatable query" error.. but am still getting the same error..

Please to help!

[orange]

What do you get when you run this

select nd2.Node_Alias
from CAXNODE nd2
where nd2.Node_mode = 'LOGICAL' and Partition_Type = 'LDOM'

I do not have Acc2007.
Do you have a lot of records?
Can you send a copy of db in mdb format?

I created a table using your data. I used your query and I get a data type mismatch???

[nkshirsa]

zipped up the rtf .. open wordpad and open file after unzipping (dont drag and drop)

let me know if you have problems opening it.. caxnode table has just 11 rows.. so i can even copy/paste the important fields in email if that works better..

the two queries i tried so far are..

UPDATE caxnode AS nd SET Node_mode = 'LOGICAL', Partition_Type = 'LDOM', Host_ID = nd2.Host_ID, Num_of_proc = nd2.Num_of_proc
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 nd2.Partition_Type = 'LDOM'
);


UPDATE caxnode
SET caxnode.Host_ID = (select host_id from caxnode b where caxnode.node_alias=b.node_alias and b.partition_type='LDOM'),
caxnode.partition_type='LDOM'
WHERE (Node_mode = 'VIRTUAL' or Node_mode = 'REGULAR');

both work when double clicked, but when i try to "run" them they manually ask me to enter value of nd2.host_id in first case .. or b.node_alias in second case..

[nkshirsa]

this is what i get when i run the query you asked me to run..

Node_Alias
pun-ldom-rds131
pun-ldom-rds133
pun-ldom-rds134
pun-rds-sun22

[nkshirsa]

basically i need to identify the node_aliases that are also marked ldoms in the same table, and mark them as ldoms too .. thats it!

[nkshirsa]

and mark the rest of their fields the same as the ones as the one marked ldom ..

for eg.. if pun-ldom-rds131 is marked LDOM somewhere in the table, i need to mark the row with alias=pun-ldom-rds131 as node_type=ldom, host_id=host_id_of_that_row <-- thisis where the problem is..

[orange]

Based on your original post I created a table CAXNODE
but you have fields in your queries that are not in the original.

Node_TYPE ????

Tried opening your zip --- no luck. It attempted to open the rtf
but it has unknown info on right side, nothing in the columns with headings????

Latest, did open with Wordpad. Am looking at the data

I am not seeing field separators -- fields just run together.
I can't get the data into a deleimited, or fixed format that I can import to work with.

What does this mean

Node_ID INDEXX Node_descr
pun-ldom-rds131U6SQjnbGMh22lY

When I try the import I get errors??????

caxnode_ImportErrors Error Field Row Type Conversion Failure Serial_Number 1 Type Conversion Failure Serial_Number 2 Type Conversion Failure Serial_Number 3 Type Conversion Failure Serial_Number 4 Type Conversion Failure Serial_Number 5 Type Conversion Failure Serial_Number 6 Type Conversion Failure Serial_Number 7 Type Conversion Failure Serial_Number 8 Type Conversion Failure Serial_Number 9 Type Conversion Failure Serial_Number 10 Type Conversion Failure Serial_Number 11 Type Conversion Failure Serial_Number 12

[nkshirsa]

tell me an email address i can mail you the mdb at.. its about 5 mb .. got lots of other tables which are not used in the query.. otherwise i can try to create a new mdb with one table.. let me know which you prefer..

[orange]

Please see my latest PM.

I need to know the criteria exactly. I did load data (with errors as posted) and can get the same records as you when running your subquery from post #3

select nd2.Node_Alias
     from CAXNODE nd2
     where nd2.Node_mode = 'LOGICAL' and 
nd2.Partition_Type = 'LDOM'

Your attempted Update queries seem to have different criteria.
I am not familiar with your data, so need to know exactly what criteria to use etc.

[nkshirsa]

made a new mdb with that one table with the relevant fields.. now you will get a clear idea..

[orange]

I have the database.
What specifically, in detail, do you want updated, and under what criteria?

Details please, I am not familiar with your data.

[nkshirsa]

My query should update all the rows which have the same node_alias also defined as an ldom and update their other fields like this..

host_id should become the ldom's host id
partition_Type should become ldom from null
node_mode should become logical from virtual/regular
num_of_proc should become the same value that is defined in the ldom

[nkshirsa]

the best attempt i have made so far is UPDATE caxnode AS nd SET Node_mode = 'LOGICAL', Partition_Type = 'LDOM', Host_ID = nd2.Host_ID, Num_of_proc = nd2.Num_of_proc
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 nd2.Partition_Type = 'LDOM'
);

but this asks me to manually enter nd2.host_id when i try to run the query in access..