help with update query with select from same table

[nkshirsa]

try working with the mdb i created with one table. thats exactly the fields i want updated.. rest of the fields stay the same

[nkshirsa]

i've pasted the data below.. appologies for not ordering it, but i have sent you the same in a mdb in the attachment on the thread..

now.. here's what i want..

id 1 is already ldom.. (node_mode is logical, partition_type is ldom) no change.
id2 is regular mode, partition_Type is null, but not marked as ldom anywhere else in the table.. no change..
id3 is ldom, no change
id4 is ldom, id5 is ldom, no change

id6 is regular and null partition type, BUT marked as ldom elsewhere (id 1) so change it accordingly..

same with id 7,8,9,10,11 and 12 .. their alias is also found marked as partition_Type LDOM, so they need to change.. with the appropriate host_id and num_of_procs ..

so id 6, 7,8,9,10 will be marked as partition_type LDOM, node_mode as LOGICAL, host_id=host_id of id 1, num_of_procs=num_of_procs of id1 (pun-rds-sun22)

id 11 will become partition_type LDOM, node_mode=logical, host_id=host_id of id 3, num_of_procs of id 3 (because id 3 has same node_alias marked as LDOM)

id 12 willbecome partition_type LDOM, node_mode=logical, host_id=host_id of id 4, num_of_procs of id4

let me know if i need to explain again .. i'm sorry for making it seem so confusing.. its simply actually.. just see if the alias is defined as ldom elsewhere, if it is, update this row to that row..


ID node_id host_id node_mode num_of_proc node_alias partition_type
1 pun-ldom-rds131U6SQjnbGMh22lY pun-rds-sun22 LOGICAL 128 pun-ldom-rds131 LDOM
2 pun-ldom-rds132U6SQ[nbGMh2Y8A pun-ldom-rds132 REGULAR 8 pun-ldom-rds132
3 pun-ldom-rds133U6SQOnbGMh2Epj pun-rds-sun22 LOGICAL 128 pun-ldom-rds133 LDOM
4 pun-ldom-rds134U6SQCnbGMh2Epj pun-rds-sun22 LOGICAL 128 pun-ldom-rds134 LDOM
5 pun-rds-sun22 UpS_USvA))06A pun-rds-sun22 LOGICAL 128 pun-rds-sun22 LDOM
6 pun-ldom-rds131U6SQjnbasdfasdf pun-ldom-rds131 REGULAR 16 pun-ldom-rds131 NULL
7 pun-ldom-rds131U6SQjnbsdsasdas pun-ldom-rds131 VIRTUAL 16 pun-ldom-rds131 NULL
8 pun-ldom-rds131U6SQjnbsdsasqwe pun-ldom-rds131 VIRTUAL 16 pun-ldom-rds131 NULL
9 pun-ldom-rds131U6SQjnbsdswwww pun-ldom-rds131 REGULAR 16 pun-ldom-rds131 NULL
10 pun-ldom-rds131U6SQjnbsdswwaaa pun-ldom-rds131 REGULAR 16 pun-ldom-rds131 NULL
11 pun-ldom-rds133U6SQOnbGMh2EpJJ pun-ldom-rds133 VIRTUAL 128 pun-ldom-rds133 NULL
12 pun-ldom-rds134U6SQCnbGhhhhhhh pun-ldom-rds134 REGULAR 12 pun-ldom-rds134 NULL

[orange]

I did work with your database:
This is the Update query

UPDATE Caxnode AS A INNER JOIN Caxnode AS B
ON a.node_alias=b.node_alias
SET a.partition_Type = b.partition_type
, a.node_mode = b.node_mode
, a.host_id = b.host_id
, a.num_of_proc = b.num_of_proc
WHERE (b.partition_type)="LDOM";

I hope this is what you wanted.

See PM for data

[nkshirsa]

many thanks..

will this work on oracle as well ?

[orange]

many thanks..

will this work on oracle as well ?

Glad to help.

Yes, I think it should work with Oracle as well.

[nkshirsa]

cant thank you enough!

[nkshirsa]

i modified it to not update ldoms .. here's what i have now..

UPDATE Caxnodebackup4 AS A INNER JOIN Caxnodebackup4 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'));

[orange]

Is this a new issue? Change in requirements? Or you're solving another?

[nkshirsa]

same issue, just dont want to update ldom with ldom.. redundant.. so putting a condition for regular/virtual only..

[nkshirsa]

@orange this doesnt work in oracle, oracle unfortunately doesnt allow inner join with update .. so im trying to now get it working in oracle.. if you have an idea do let me know!

[orange]

I wasn't aware of the Oracle issue. I am just doing a little research on it now.

Which is the SQL (the MS ACCESS code) that you are trying to use with Oracle?
What version of Oracle?
I hope you're working on a test database or test table!!!

[nkshirsa]

this is what i came up with for oracle.. i think it works.. let me know if u see a problem..

update CAXNODE nd1 set Node_mode = 'LOGICAL',Partition_Type = 'LDOM',
(Host_ID, Num_of_proc) = (select Host_ID, Num_of_proc
from CAXNODE nd2
where nd2.Node_mode = 'LOGICAL' and nd2.Partition_Type = 'LDOM' and nd1.Node_Alias= nd2.Node_Alias)
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 found this site
http://download.oracle.com/docs/cd/B...7.htm#i2189756
that has an example of multifield update with subquery.

I think your statement matches the example,
but I haven't any means to test it.

I would set up a test by copying your data to a test table.
They try your update statement.
If your test works, do it on your real table.
Good luck.

[orange]

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

You're losing me. I thought according to your posts that you had Access working but could not use the same code in Oracle. Then you showed what you had for Oracle (and it looked OK to me but since NO Oracle I couldn't test) and you were going to try to run in Oracle.

Where is this newest stuff for Access coming from?

Access won't know what the

Host_ID = nd2.Host_ID, Num_of_proc = nd2.Num_of_proc

is since nd2 is in the subquery.

Is this a separate issue from Oracle?
Do you want this query to work in Access?

[orange]

nkshirsa

I don't know if you ever resolved this, but I did find a reference for Updating Oracle where there was a joined table.

see:http://decipherinfosys.wordpress.com...another-table/