Results 1 to 5 of 5

Thread: update problem

  1. #1
    Join Date
    Feb 2009
    Posts
    38

    Unanswered: update problem

    [!][!]I want to update a table CLIENTS(code_client,name,surname,cleio (number)).
    Created a view on it named TRIP which has only 2 clients_codes (those whose cleio is over 100).
    I must now abstract 100 from CLIENTS.CLEIO where CODE_CLIENT from TRIP matches CODE_CLIENT from CLIENTS. Did this but of course doesnt work:


    UPDATE CLIENT
    SET CLIENT.CLEIO=TRIP.CLEIO-CLIENT.CLEIO
    FROM CLIENT
    JOIN TRIP
    ON CLIENT.CODE_CLIENT=TRIP.CODE_CLIENT
    GROUP BY CLIENT.CODE_CLIENT

    Why cant it run?

    Thank you.

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by misty1976
    Why cant it run?
    Because it is syntactically incorrect in Oracle.
    You may find the correct syntax in SQL Reference book. It is available with other Oracle documentation e.g. online on http://tahiti.oracle.com/.

    You may alternatively search in this forum. This thread may be interesting for you: http://www.dbforums.com/oracle/16299...mn-update.html

  3. #3
    Join Date
    Jan 2009
    Location
    Dhaka, Bangladesh
    Posts
    51
    Quote Originally Posted by misty1976
    [!][!]I want to update a table CLIENTS(code_client,name,surname,cleio (number)).
    Created a view on it named TRIP which has only 2 clients_codes (those whose cleio is over 100).
    I must now abstract 100 from CLIENTS.CLEIO where CODE_CLIENT from TRIP matches CODE_CLIENT from CLIENTS. Did this but of course doesnt work:


    UPDATE CLIENT
    SET CLIENT.CLEIO=TRIP.CLEIO-CLIENT.CLEIO
    FROM CLIENT
    JOIN TRIP
    ON CLIENT.CODE_CLIENT=TRIP.CODE_CLIENT
    GROUP BY CLIENT.CODE_CLIENT

    Why cant it run?

    Thank you.
    join in not possible in update statement, please try in this way

    UPDATE CLIENT
    SET CLIENT.CLEIO=( SELECT TRIP.CLEIO-CLIENT.CLEIO
    FROM TRIP WHERE CLIENT.CODE_CLIENT=TRIP.CODE_CLIENT)
    WHERE EXISTS (SELECT TRIP.CLEIO-CLIENT.CLEIO
    FROM TRIP WHERE CLIENT.CODE_CLIENT=TRIP.CODE_CLIENT);
    Mohammad Hasan Shaharear
    E-mail
    Blog: http://shaharear.blogspot.com

  4. #4
    Join Date
    Feb 2009
    Posts
    38
    Quote Originally Posted by hasan_uiu
    join in not possible in update statement, please try in this way

    UPDATE CLIENT
    SET CLIENT.CLEIO=( SELECT TRIP.CLEIO-CLIENT.CLEIO
    FROM TRIP WHERE CLIENT.CODE_CLIENT=TRIP.CODE_CLIENT)
    WHERE EXISTS (SELECT TRIP.CLEIO-CLIENT.CLEIO
    FROM TRIP WHERE CLIENT.CODE_CLIENT=TRIP.CODE_CLIENT);
    That did it. Thank you very much.

  5. #5
    Join Date
    Feb 2009
    Posts
    62
    You could try updating a view, like this:

    Code:
    create table test_110 (col_1 number, col_2 number);
    
    create table test_111 (col_1 number, col_2 number);
    
    insert into test_110 select level,null from dual connect by level <= 5;
    
    insert into test_111 values (1,5);
    insert into test_111 values (2,3);
    
    update (select t0.col_2  orig_col_2
                 ,(select t1.col_2 from test_111 t1 where t1.col_1 = t0.col_1) new_col_2
            from   test_110 t0)
    set orig_col_2 = new_col_2;        
    
    select * from test_110;

Posting Permissions

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