Results 1 to 2 of 2
  1. #1
    Join Date
    May 2004
    Posts
    184

    Unanswered: UPDATE return error over NULL

    I have an UPDATE query that works fine as long as the jobCategory table has less records than the the number of distinct records in userTemp, but when the counts are reversed, a null value appears in the updated values...

    Code:
    update jobCategory jc set title = (select distinct jobfamily from
    usertemp where
    UPPER(jc.code)=UPPER(userTemp.jobCategoryCode))
    jobCategory has 19 records and usertemp has 7 records and error is generated about null value in title column being inserted.


    The following modification works, but I'm not totally sure why the first statement doesn't work... I thought the where clause would limit me down only to matching values but instead it is overwriting existing non matching records with Null values.

    Code:
    update jobCategory jc 
    set title = (select distinct jobfamily from
        usertemp where
                UPPER(jc.code)=UPPER(userTemp.jobCategoryCode))
    where exists 
          (select  * from usertemp
          where UPPER(jc.code)=UPPER(userTemp.jobCategoryCode))
    Any help would be appreciated.

    Thank you.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    In the 1st SQL statement
    >where UPPER(jc.code)=UPPER(userTemp.jobCategoryCode))

    Is part of the SELECT clause & is not used to qualify the UPDATEd records.
    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.

Posting Permissions

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