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!

BTW, I meant to say the "composite" key, not "compound". :)

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.

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!

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)
;

---

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...

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)
;

---

I tried that too and still the same error... Boggles my mind! :confused:

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!!