Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2011
    Posts
    4

    Unanswered: update with a join in oracle sql

    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.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >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;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2011
    Posts
    3

    Oracle Update Statement with joining other tables

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

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Apr 2011
    Posts
    3

    Update

    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.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Can you let me know if we can update at all with joining the tables.
    yes, when done correctly
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Apr 2011
    Posts
    3

    Update

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

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    which column in which table needs to SET to what value based upon which conditions being true
    in this and any other tables?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •