Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2009
    Posts
    2

    Unanswered: Need Help with Update query

    I need to update records where records with the same ids in the same exists with certain conditions.

    Data example:
    Table name sc_base.hcfa_date
    ID name_id Preferred Indicator Start_date End_date
    1 9000 subsidy_level 01-JAN-09 31-DEC-09
    2 9000 x subsidy_level 01-JAN-10 31-DEC-10

    So I need to update every 2009 record that is NOT preferred with an ‘x’ if a preferred 2010 record exists.

    update sc_base.hcfa_date a
    set a.preferred =
    (select hd1.preferred
    from sc_base.hcfa_date hd, sc_base.hcfa_date hd1
    where hd.name_id= hd1.name_id
    and hd.preferred is null
    and hd.indicator = 'subsidy_level'
    and to_char(hd.start_date,'YYYYMMDD')='20090101'
    and to_char(hd.end_date,'YYYYMMDD')='20091231'
    and hd1.preferred ='x'
    and hd1.indicator = 'subsidy_level'
    and to_char(hd1.start_date,'YYYYMMDD')='20100101'
    and to_char(hd1.end_date,'YYYYMMDD')='20101231')
    where a.preferred is null
    and a.indicator = 'subsidy_level'
    and to_char(a.start_date,'YYYYMMDD')='20090101'
    and to_char(a.end_date,'YYYYMMDD')='20091231'

    The query does not error out but it does not stop running. There are only 40,000 records in this test table and only one that meet this criteria.

    Any help would be appreciated.
    Thanks -HM

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    UPDATE sc_base.hcfa_date a
    SET    a.preferred = (SELECT hd1.preferred
                          FROM   sc_base.hcfa_date hd,
                                 sc_base.hcfa_date hd1
                          WHERE  hd.name_id = hd1.name_id
                                 AND hd.preferred IS NULL
                                 AND hd.INDICATOR = 'subsidy_level'
                                 AND To_char(hd.start_date,'YYYYMMDD') = '20090101'
                                 AND To_char(hd.end_date,'YYYYMMDD') = '20091231'
                                 AND hd1.preferred = 'x'
                                 AND hd1.INDICATOR = 'subsidy_level'
                                 AND To_char(hd1.start_date,'YYYYMMDD') = '20100101'
                                 AND To_char(hd1.end_date,'YYYYMMDD') = '20101231')
    WHERE  a.preferred IS NULL
           AND a.INDICATOR = 'subsidy_level'
           AND To_char(a.start_date,'YYYYMMDD') = '20090101'
           AND To_char(a.end_date,'YYYYMMDD') = '20091231'
    using TO_CHAR on DATEs preclude the use of any index on those fields.
    You should be using TO_DATE on the strings instead.

    AND hd.preferred IS NULL -- guarentees a Full Table Scan

    post EXPLAIN PLAN for this SQL
    Last edited by anacedent; 08-19-09 at 16:52.
    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
    Aug 2009
    Posts
    2
    I’m trying to update any records in my table sc_base.hcfa_date

    where 2009.preferred is null
    and 2009.indicator = 'subsidy_level'
    and to_date('01/01/2009', 'MM/DD/YYYY') =2009.start_date
    and to_date('12/31/2009', 'MM/DD/YYYY') =2009.end_date
    and where exists
    2010.preferred ='x'
    and 2010..indicator = 'subsidy_level'
    and to_date('01/01/2010', 'MM/DD/YYYY') =2010..start_date
    and to_date('12/31/2010', 'MM/DD/YYYY') =2010..end_date)

    I have 2010 records marked with an ‘x’ in the preferred field and I need that field set = ‘’ or null and I need the 2009 preferred to be ‘x’

    So in sc_base.hcfa_date
    What I have
    ID Name_id Preferred Indicator Start_date End_date
    1 9000 subsidy_level 01/01/2009 12/31/2009
    2 9000 x subsidy_level 01/01/2010 12/31/2010

    What I need
    ID Name_id Preferred Indicator Start_date End_date
    1 9000 x subsidy_level 01/01/2009 12/31/2009
    2 9000 subsidy_level 01/01/2010 12/31/2010

    So I need to update the record where a preferred 2010 record exists

    I know what you mean with the “is null” but when I run

    select 1
    from sc_base.hcfa_date hd, sc_base.hcfa_date hd1
    where hd.name_id= hd1.name_id
    and hd.preferred <>’x’
    and hd.indicator = 'subsidy_level'
    and to_date('01/01/2009', 'MM/DD/YYYY') =hd.start_date
    and to_date('12/31/2009', 'MM/DD/YYYY') =hd.end_date
    and hd1.preferred ='x'
    and hd1.indicator = 'subsidy_level'
    and to_date('01/01/2010', 'MM/DD/YYYY') =hd1.start_date
    and to_date('12/31/2010', 'MM/DD/YYYY') =hd1.end_date

    I get no records

    But if I run

    select 1
    from sc_base.hcfa_date hd, sc_base.hcfa_date hd1
    where hd.name_id= hd1.name_id
    and hd.preferred is null
    and hd.indicator = 'subsidy_level'
    and to_date('01/01/2009', 'MM/DD/YYYY') =hd.start_date
    and to_date('12/31/2009', 'MM/DD/YYYY') =hd.end_date
    and hd1.preferred ='x'
    and hd1.indicator = 'subsidy_level'
    and to_date('01/01/2010', 'MM/DD/YYYY') =hd1.start_date
    and to_date('12/31/2010', 'MM/DD/YYYY') =hd1.end_date

    I get one as expected

    ..sry for the bad formatting..

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Post Operating System (OS) name & version for DB server system.
    Post results of
    SELECT * from v$version

    Post DDL for table.

    Which columns have indexes on them?

    Are statistics current for table & indexes?

    invoke sqlplus
    SQL> SET AUTOTRACE ON TRACEONLY EXPLAIN STATISTICS
    SQL> -- INVOKE UPDATE sc_base.hcfa_date a ...

    cut SQL & results and PASTE back here using <code tags>
    as describe in #1 STICKY post at top of this forum
    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
    Jul 2003
    Posts
    2,296
    I agree with Ana - you should never apply functions to the column names in the WHERE clause. Always apply functions against your data values. If you want a whole day of date/times then use the BETWEEN clause.

    This is also the case for anything where you have "IS NULL" "IS NOT NULL". You are better off setting default values for columns that will be null in order to leverage your indexes. Only other solution for performance is to create function based indexes.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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