Geek asks and answers

Upgrading SQL Server Using Multiple Tables

I don't know much about SQL — I'm just writing an RFID registration registrar that interacts with a SQL Server database.

I am sure this is fairly easy to do, but I could not find a solution to my problem.

I want to be able to do something like this in a basic form that won't work:

UPDATE Attendance 
SET    A1 = 'TRUE' 
WHERE  Student.ID = '3a0070c48' 
  AND  Module.Day = 'Friday' ;

But a full SQL update should be something like this:

UPDATE Attendance 
SET    A1 = 'TRUE' 
WHERE  Student.ID = '3a0070c48' 
  AND  Module.Day = 'Friday' 
  AND  '1100' BETWEEN Module.StartTime 
                  AND Module.EndTime ;
+5
source share
1 answer

Ok, you need to do something like this:

UPDATE A
SET A.A1 = 'TRUE' 
FROM Attendance A
INNER JOIN Student S
    ON A.StudentId = S.ID
INNER JOIN Module M
    ON A.ModuleId = M.ID
WHERE S.ID = '3a0070c48' 
AND M.[Day] = 'Friday' 
AND '1100' BETWEEN M.StartTime AND M.EndTime

I figured out the columns associated with your tables, but it should be very close to what you have, you need to use real columns.

+7
source

More articles:


All Articles