-
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.
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.
-
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)
;
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.
-
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)
;
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.
-
I tried that too and still the same error... Boggles my mind!
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|