CODEWITHSUNDEEP
sqlms-access
user5784310
user5784310

Reputation:

Updating a table from another table

I have a query down below which works. My question is I cant seem to alter it so it updates the LessonTaken field in Availability Table everytime unless StudentID=0. So only want it to update the field if the studentID <> 0;

UPDATE Availability SET LessonTaken = 'Y'
WHERE (
   SELECT LessonID
   FROM Lesson
   WHERE Availability.StudentID = Lesson.StudentID
);

The Tables are like so:

Availability:

AvailabilityID StudentID StartTime EndTime LessonTaken NoOfFrees

Lesson:

LessonID StudentID StartTime EndTime DayOfWeek LessonPaid.

I have a query which selects the student with the fewest frees, (selecting DayOfWeek, StartTime, EndTime) and inserts this into the LessonTable for the corresponding fields. This is for a timetabling programme. I hope this is clear, many thanks :)

Upvotes: 0

Views: 69

Answers (3)

Juan Nenna
Juan Nenna

Reputation: 21

this might sound silly, but have you tried: 

UPDATE Availability SET LessonTaken = 'Y'
WHERE (SELECT LessonID
       FROM Lesson
       WHERE Availability.StudentID = Lesson.StudentID
       AND Lesson.StudentID != 0 
       );

?? Hope it helps!

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Does adding the condition you want help?

UPDATE Availability
    SET LessonTaken = 'Y'
WHERE Availability.studentID <> 0 AND
      (SELECT LessonID
       FROM Lesson
       WHERE Availability.StudentID = Lesson.StudentID
      );

Upvotes: 0

Veljko89
Veljko89

Reputation: 1953

This is for T-SQL, using join

update avail
    set LessonTaken = 'Y' 
from Availability avail
    join Lesson less on avail.StudentID = less.StudentID
where avail.StudentID <> 0

Good luck

Upvotes: 1

Related Questions