update using subquery
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 26

Thread: update using subquery

  1. #1
    Join Date
    Jun 2000
    Posts
    315

    update using subquery

    I need to update a huge table with many years of data.
    The data I need to update is for year 2004. The script I wrote is:

    update /*+PARALLEL */ facttable f set (a, b) = (select a, b from load_facttable)
    where c=f.c)

    This script would update the whole table. How could I make it only update data only for year 2004?

    Thanks.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333

    Re: update using subquery

    Originally posted by lgaorcl
    I need to update a huge table with many years of data.
    The data I need to update is for year 2004. The script I wrote is:

    update /*+PARALLEL */ facttable f set (a, b) = (select a, b from load_facttable)
    where c=f.c)

    This script would update the whole table. How could I make it only update data only for year 2004?

    Thanks.
    by adding a where clause on facttable which denotes 2004 data

  3. #3
    Join Date
    Jun 2000
    Posts
    315
    The problem is this script would update those not match c=f.c records to be null. How could I avoid this? I don't really have spefic year, just trying to use c=f.c to update a and b column.

    Thanks.

  4. #4
    Join Date
    Jan 2004
    Posts
    162
    If loadfacttable is unique by 'c' you might consider...
    Code:
    UPDATE (
      SELECT f.a, f.b, 
             l.a new_a, l.b new_b
      FROM   loadfacttable l, facttable f
      WHERE  f.c = l.c)
    SET a = new_a, b = new_b;

  5. #5
    Join Date
    Jun 2000
    Posts
    315
    Padders: Thank you very much! I test the script, it seems it worked.

    One more question: How can select a from date_table where full_date='11/30/2004'? full_date column datatype is date.

    I really appreciate your help!

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    Originally posted by lgaorcl
    Padders: Thank you very much! I test the script, it seems it worked.

    One more question: How can select a from date_table where full_date='11/30/2004'? full_date column datatype is date.

    I really appreciate your help!
    where full_date = to_date('11/30/2004','DD/MM/YYYY')

  7. #7
    Join Date
    Jun 2000
    Posts
    315
    Davey: I tried that one. It gave me error message: ora-1843: not a valid month.

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Can't you spot the typo when someone suggests something so trivial as in above, particualry when the error code says it so clearly?? Can't you use some plain common sense and try:

    where full_date = to_date('11/30/2004','MM/DD/YYYY')
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Jun 2000
    Posts
    315
    jmodic: you are funny, but you are right. I got it right!

    Thanks!

  10. #10
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    where full_date = to_date('11/30/2004','DD/MM/YYYY')
    It aint a typo if you live in the UK

Posting Permissions

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



Click Here to Expand Forum to Full Width