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 doesnt work with inner join in oracle..

can someone please help me translate this query to work on oracle?

Thanks!
-nikhil.

>This doesn't work in oracle,

do as below so we can know complete Oracle version & OS name.

Post via COPY & PASTE complete results of
SELECT * from v$version;

Hi,

I have been googling a lot for the same issue. My question is here.

I have to update a table's column based on some conditions with its relation to the other 3 tables using oracle DB .I have tried various ways for this.

Here is my table, just displaying one of the rows. I have 3 other tables with a key in them. I have to join on those keys with other tables form this table and update the status column to other value.

status is the name of the table and it has a column called status.

select * form status;
KEY TIMESTAMP STATUS
110 2011-04-12 13:45:16 pending since 4/12/11 1:45:16 PM

I have tried the following ways:
################

update status as A, WEBMDOCUMENT AS D, TOMTS AS B, CUSTOMERS AS C SET status='rejected on 4/15/11 2:02:10 PM' WHERE A.KEY = D.KEY
AND A.KEY=B.KEY AND B.KEY=C.KEY AND a.status like 'pending since%' and C.CUSTOMER_NAME='TST1';

###########

update status set A.status='rejected on 4/14/11 2:02:10 PM' from status A
join WEBMDOCUMENT D on A.KEY = D.KEY
join TOMTS B on A.KEY=B.KEY
join CUSTOMERS C on C.KEY=B.KEY where TO_CHAR(A.TIMESTAMP,'YYYY-MM-DD')=TO_CHAR(SYSDATE,'YYYY-MM-DD') and
and A.status like 'pending since%' and
C.CUSTOMER_NAME='TST1';

We don't have your tables.
We don't have your data.
We don't know your requirements.
I have no basis to decide what is correct solution & what is flawed SQL.
How many rows does the UPDATE statement change?
How many rows should the UPDATE statement change?

It is not updating any columns at all. First of all, Does the update allow to join on other tables. Forget about the tables. I have searched various links and found the above mentioned update statements. But they do not seem to work.

Can you let me know if we can update at all with joining the tables.

>Can you let me know if we can update at all with joining the tables.
yes, when done correctly

Please let me know which data I can provide you.

My customers table has the following columns

key AND TIMESTAMP like wise the other 2 tables have the same columns.

And I have the data displayed whne i use the following select statement.


select A.* from status A
join WEBMDOCUMENT D on A.KEY = D.KEY
join TOMTS B on A.KEY=B.KEY
join CUSTOMERS C on C.KEY=B.KEY where
and A.status like 'pending since%' and
C.CUSTOMER_NAME='TST';

which column in which table needs to SET to what value based upon which conditions being true
in this and any other tables?