SQL Update Issue
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: SQL Update Issue

  1. #1
    Join Date
    Feb 2010
    Posts
    6

    SQL Update Issue

    I'm trying to run an update statement to update the exp_date column in the COURSES table based on the exp_date in the NEW_COURSES table. I only want to update the column if the the exp_date in the two tables don't match. The problem is that I keep getting an error saying I can't update the exp_date to null (exp_date is a non-nullable column). I've checked several times that neither tables contain null exp_dates.

    The compound key on both tables is user_id, course_id, and start_date.

    Here's the update statement I'm using:
    UPDATE COURSES
    SET EXP_DATE = (SELECT A.EXP_DATE FROM NEW_COURSES A
    WHERE A.USER_ID = COURSES.USER_ID AND A.COURSE_ID = COURSES.COURSE_ID AND A.START_DATE = COURSES.START_DATE)
    WHERE EXISTS (SELECT A.EXP_DATE FROM NEW_COURSES A
    WHERE A.USER_ID = COURSES.USER_ID AND A.COURSE_ID = COURSES.COURSE_ID AND A.START_DATE = COURSES.START_DATE AND A.EXP_DATE != COURSES.EXP_DATE);

    Does anyone have an idea of what I'm doing wrong?? Let me know if you need more information.

    Thanks in advance for your help!

  2. #2
    Join Date
    Feb 2010
    Posts
    6
    BTW, I meant to say the "composite" key, not "compound".

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    This looks like homework but you are trying - we appreciate that.

    1- Just one inline view would solve it; no need for two.

    2- Since PK in both tables is (user_id, course_id, and start_date) chances are tables have more than one column with identical (user_id, course_id) values. Question is... which NEW_COURSES row should be choosen to update COURSES row? the one with the higher start_date?

    3- Think about what should query do if for a give courses(user_id,course_id) there is no matching row on new_courses table.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Feb 2010
    Posts
    6
    Thanks for your response! Here's a little more information on what I'm trying to accomplish... I only want to update the records in COURSES where there is a matching NEW_COURSES record (based on the PK) AND where the exp_date has changed in NEW_COURSES. I don't want to touch any records where this criteria isn't met.

    So in answer to your question in #2, I want to choose the NEW_COURSES row where the user_id, course_id, and start_date are matching in COURSES. Since start_date is part of the PK, I didn't think I had to do anything other than join on those three key fields to get the matching records. Am I not understanding your question correctly?

    In answer to #3, if there is no matching row in NEW_COURSES then I don't want to do anything to the record in COURSES.

    I really appreciate your help!

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    I would start with something like...
    Code:
    update  courses c
    set     c.exp_date = (select  n.exp_date
                          from    new_courses n
                          where   c.user_id    =  n.user_id
                          and     c.course_id  =  n.course_id
                          and     c.start_date =  n.start_date
                          and     c.exp_date   <> n.exp_date)
    ;
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Feb 2010
    Posts
    6
    Thanks! However I went down this path already and it doesn't handle the scenario of not applying an update to records that exist in COURSES but not in NEW_COURSES. I still get the "can't update to null" error because I believe it attempts to update those records that don't exist in NEW_COURSES to null. That's how I ended up with the "where exists" check...

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Sorry, I missread your original posting - may be unformatted code got me confused but it was my fault anyway.

    How about...
    Code:
    update  courses c
    set     c.exp_date = (select  n.exp_date
                          from    new_courses n
                          where   c.user_id    =  n.user_id
                          and     c.course_id  =  n.course_id
                          and     c.start_date =  n.start_date
                          and     c.exp_date   <> n.exp_date)
    where   exists       (select  1
                          from    new_courses m
                          where   c.user_id    =  m.user_id
                          and     c.course_id  =  m.course_id
                          and     c.start_date =  m.start_date
                          and     c.exp_date   <> m.exp_date)
    ;
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  8. #8
    Join Date
    Feb 2010
    Posts
    6
    I tried that too and still the same error... Boggles my mind!

  9. #9
    Join Date
    Feb 2010
    Posts
    6
    I think I'm onto something but I'm still unsure as to the root of the issue... I realized that I didn't mention that the new_courses "table" is actually a view (because I forgot it was one!). So, I tried copying the data from the new_courses view into an actual table. I then modified the update statement to use the new_courses table and it worked!! I'm still confused as to why it would work without error as a table but not as a view. The data is the same in both so it's should update in the same way, right?

    This is a pain since we have to add yet another table to our database, but I guess we have to if there's no other choice. So any insight as to why the update statement works using a table vs. a view is greatly appreciated!!

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