Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2002
    Location
    beijing,china
    Posts
    20

    Red face Unanswered: strange things??!!

    just flow me,

    SQL> select * from aaa;

    ID NAME
    ---------- ----------
    1 1
    2 2
    3 3
    4 4
    5 5
    6 6

    6 rows selected

    SQL> select * from bbb;

    ID NAME
    ---------- ----------
    1 aaaa
    2 bbbb
    3 cccc
    4 dddd
    55 xxxx
    66 xxxx
    77 xxxx

    7 rows selected

    SQL> update bbb set name=(
    2 select name from aaa
    3 where aaa.id=bbb.id);

    7 rows updated

    SQL> select * from bbb;

    ID NAME
    ---------- ----------
    1 1
    2 2
    3 3
    4 4
    55
    66
    77

    7 rows selected

    SQL>
    We can see that, last three lines was set null, why?
    Is this an Oracle bug?

    who can I do it correctly, then?
    db2oracle

  2. #2
    Join Date
    Jan 2003
    Location
    Duncan BC Canada
    Posts
    80
    You have no where clause in your update statement. Therefore, all rows in table bbb were updated. For those rows where there was no matching row in aaa, there was no result to use for the assignment to bbb.name, hence the NULL.
    Bradley

  3. #3
    Join Date
    Apr 2002
    Location
    beijing,china
    Posts
    20
    yes, you are right.

    use

    update bbb set name=(
    select name from aaa
    where aaa.id=bbb.id )
    where exists( select 1 from aaa where aaa.id=bbb.id )

    it works, but it seems it runs very slowly,
    I have tow big tables(>20krows), anyone has more faster sql?
    db2oracle

  4. #4
    Join Date
    Feb 2003
    Location
    Cleveland, OH
    Posts
    4
    What's the purpose of the select statement?

    If, as a rule, the value of bbb.name needs to reflect the value of aaa.name, maybe a trigger would be better. That would prevent having to do the updates all at once.

    Or is this some sort of job that needs to run regularly in a batch manner?

    If it only needs to be run once, does it matter that it's slow? I assume both the ID fields are keys and are indexed.

    I'm curious why you have two tables with both an id and name field, apparently describing the same thing?

  5. #5
    Join Date
    Feb 2003
    Posts
    2

    Re: strange things??!!

    update bbb set name=(
    select name from aaa
    where aaa.id=bbb.id);

    should update the first 6 rows and set the 7th to null.

    I tried it and it works.




    Originally posted by kerlion
    just flow me,

    SQL> select * from aaa;

    ID NAME
    ---------- ----------
    1 1
    2 2
    3 3
    4 4
    5 5
    6 6

    6 rows selected

    SQL> select * from bbb;

    ID NAME
    ---------- ----------
    1 aaaa
    2 bbbb
    3 cccc
    4 dddd
    55 xxxx
    66 xxxx
    77 xxxx

    7 rows selected

    SQL> update bbb set name=(
    2 select name from aaa
    3 where aaa.id=bbb.id);

    7 rows updated

    SQL> select * from bbb;

    ID NAME
    ---------- ----------
    1 1
    2 2
    3 3
    4 4
    55
    66
    77

    7 rows selected

    SQL>
    We can see that, last three lines was set null, why?
    Is this an Oracle bug?

    who can I do it correctly, then?

  6. #6
    Join Date
    Feb 2003
    Posts
    2

    Re: strange things??!!

    Sorry. I meant update the first 4 and set the last 3 to null.

    I would suggest the following statement:

    update bbb
    set name=nvl((select name from aaa
    where aaa.id=bbb.id),name)

    It updates the matching rows but leaves the other unchanged.

  7. #7
    Join Date
    Apr 2002
    Location
    beijing,china
    Posts
    20
    I tried to update a tables whith more than 2M rows,
    and about 500k rows will be updated,
    so update without where clause will change 2m rows, it will be a long time.
    I do like this, and it runs fast.

    declare
    cusor cc of
    select bbb.name
    from aaa,bbb
    where aaa.id=bbb.id
    for update aaa.name;
    begin
    for r in cc loop
    update aaa set name=r.name
    where current of cc;
    end loop;
    end;
    db2oracle

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    The UPDATE suggested earlier with a WHERE clause is around twice as fast as your PL/SQL (I am assuming ID is the PK on both tables). An IN subquery performs slightly better than the EXISTS, as the following demonstrates:

    1. Create the tables:
    Code:
    SQL> create table aaa (id, name) as select object_id, object_name from all_objects
      2  /
    
    Table created.
    
    SQL> select count(*) from aaa
      2  /
    
      COUNT(*)
    ----------
         41818
    
    SQL> create table bbb as select id, name
      2  from aaa
      3  where rownum < 10000
      4  /
    
    Table created.
    2. Add primary keys
    Code:
    SQL> alter table aaa add constraint aaa_pk primary key (id);
    
    Table altered.
    
    SQL> alter table bbb add constraint bbb_pk primary key (id);
    
    Table altered.
    3. Analyze the tables:
    Code:
    SQL> analyze table aaa compute statistics;
    
    Table analyzed.
    
    SQL> analyze table bbb compute statistics;
    
    Table analyzed.
    4. Run the tests
    Code:
    SQL> set timing on
    SQL> update aaa set aaa.name=(select bbb.name from bbb where bbb.id = aaa.id)
      2  where aaa.id in (select bbb.id from bbb);
    
    9999 rows updated.
    
     real: 6520
    
    SQL> update aaa set aaa.name=(select bbb.name from bbb where bbb.id = aaa.id)
      2  where exists (select 1 from bbb where bbb.id = aaa.id)
      3  /
    
    9999 rows updated.
    
     real: 7681
    SQL> declare
      2    cursor cc is
      3    select bbb.name
      4    from aaa,bbb
      5    where aaa.id=bbb.id
      6    for update of aaa.name;
      7  begin
      8      for r in cc loop
      9        update aaa set name=r.name
     10        where current of cc;
     11      end loop;
     12  end;
     13  /
    
    PL/SQL procedure successfully completed.
    
     real: 13880
    Generally, pure SQL should always be faster than PL/SQL.

Posting Permissions

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