Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    3

    Unanswered: Sybase to Oracle: Update syntax

    Hello all...

    Having a bit of difficulty trying ot figure out how to execute this Sybase update in Oralce...

    Any help would be greatly appreciated.

    update table_A
    set a.value_a = b.value_a,
    a.value_ba = b.value_b
    from table_A a,
    table_B b
    where a.value_x = b.value_x
    and a.value_y = b.value_y
    and a.value_z = "Z"

    Thanks in advance,

    -Dave

  2. #2
    Join Date
    Feb 2004
    Posts
    45

    Re: Sybase to Oracle: Update syntax

    Dave, the way to do this is:
    update table_A
    set a.value_a = (select b.value_a from table_B b where b.value_b = a.value_x), a.value_ba = (select b.value_b....) ;
    Or... if you want something that will run in your lifetime use PL/SQL and create a procedure with an explicit cursor, something like

    DECLARE
    local_variable_1 <<type>>;
    local_variable_2 <<type>>;
    ......
    cursor xyz is select <<whatever you need from table b>> from table_b where <<whatever>>;
    BEGIN
    OPEN xyz;
    LOOP
    FETCH xyz into local_variable_1, local_variable_2...;
    EXIT WHEN xyz%NOTFOUND;
    update table_a set ..... where table_a.value_y = local_variable_x;
    END LOOP
    CLOSE xyz;
    END;

    HTH....
    Cliff
    It was working just 5 minutes ago - I promise !

  3. #3
    Join Date
    Feb 2004
    Posts
    3
    Cliff,

    Thanks... The cursor method you stated above is the current way this is being done... I was hoping to get away from it, and get back to a simple, one-shot, SQL statement....

    I am still new to Oracle optimization techniques, but, my gut instincts tell me that a record by record interative update (as done by the cursor) has to be more time consuming, and much less efficient then a batch update (as per the Sybase query). Especially, when you start dealing with record sets well into the hundreds of thousands...

    The SQL method you first mentioned below isn't even a consideration (as you pointed out).

    If you, or anyone else, have any suggestions on optimizaing the suggested cursor technique, or another method, please let me know.

    Thanks again,
    -Dave

  4. #4
    Join Date
    Feb 2004
    Posts
    3

    bump

    just bumping it... still hoping for some help...

    Thanks in advance.

    -Dave

  5. #5
    Join Date
    Nov 2009
    Posts
    34
    Hey in the above code, can we modify the so that there is no where condition
    (not only where condition, no condition at all)

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    This works if you're updating a key-preserved table. Otherwise, you'll have to stay with one of the above techniques you've already explored:

    Code:
    SQL> create table t1 (tk  number(1) primary key, 
                          amt number (5,2));
    
    Table created.
    
    SQL> create table t2 (tk_t1    number(1) references t1(tk), 
                          main_amt number (5,2));
    
    Table created.
    
    SQL> insert into t1 values (1, 25.12);
    SQL> insert into t1 values (2, 31.25);
    SQL> insert into t2 values (1, null);
    SQL> insert into t2 values (2, null);
    
    4 rows created.
    
    SQL> select t1.tk, t1.amt, T2.main_amt
      2  from t1, t2
      3  where t1.tk = t2.tk_t1;
    
            TK        AMT   MAIN_AMT
    ---------- ---------- ----------
             1      25.12
             2      31.25
    
    SQL> UPDATE   (SELECT   t1.tk, t1.amt, T2.main_amt
      2              FROM   t1, t2
      3             WHERE   t1.tk = t2.tk_t1)
      4     SET   main_amt = amt;
    
    2 rows updated.
    
    SQL> select t1.tk, t1.amt, T2.main_amt
      2  from t1, t2
      3  where t1.tk = t2.tk_t1;
    
            TK        AMT   MAIN_AMT
    ---------- ---------- ----------
             1      25.12      25.12
             2      31.25      31.25

Posting Permissions

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