Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    23

    Unanswered: update statement syntax

    Should the following sql work?

    update IFASTTRANSACTION_TMP
    set updatedon =
    (select max(updatedon)
    from IFASTTRANSACTION_TMP t
    where t.env = IFASTTRANSACTION_TMP.env
    and t.transactionnumber = IFASTTRANSACTION_TMP.transactionnumber
    and t.transactiontype = IFASTTRANSACTION_TMP.transactiontype
    )

    I'm getting

    ora-01407 "cannot update (TA_dev.ifasttransaction_tmp.updatedon) to null

    error message when I try to execute even though there are no nulls in the updatedon field. I'm trying to make this timestamp field constant across all records for duplicates so that a select distinct statement can be used later on.

    many thanks

  2. #2
    Join Date
    Jan 2004
    Location
    Issy les Moulineaux, France
    Posts
    24
    To work, the fields env,transactionnumber and transactiontype must never be nulls.
    Otherwise, select max(updateon)...
    could return null values even if updateon is never null.

  3. #3
    Join Date
    Jan 2004
    Location
    Singapore
    Posts
    89

    Re: update statement syntax

    Originally posted by kerrie
    Should the following sql work?

    update IFASTTRANSACTION_TMP
    set updatedon =
    (select max(updatedon)
    from IFASTTRANSACTION_TMP t
    where t.env = IFASTTRANSACTION_TMP.env
    and t.transactionnumber = IFASTTRANSACTION_TMP.transactionnumber
    and t.transactiontype = IFASTTRANSACTION_TMP.transactiontype
    )

    I'm getting

    ora-01407 "cannot update (TA_dev.ifasttransaction_tmp.updatedon) to null

    error message when I try to execute even though there are no nulls in the updatedon field. I'm trying to make this timestamp field constant across all records for duplicates so that a select distinct statement can be used later on.

    many thanks
    Is the updatedon field not null, if so please check the output of the subquery, i doubt it is giving a null output.
    Thanks and Regards,

    Praveen Pulikunnu

Posting Permissions

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