SQL Server Update Using Multiple Tables [duplicate]

Question

I don't know much about SQL - I'm just writing an RFID student registration scanner that interfaces to a database in SQL Server.

I'm sure this is quite easy to do but I could not find a solution for my problem.

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

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

But the full SQL Update needs to 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 ;

'1100'? What are these columns, Varchars?

ypercubeᵀᴹ
– ypercubeᵀᴹ

2013-03-21 18:24:20 +00:00
Commented Mar 21, 2013 at 18:24 — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Mar 21, 2013 at 18:24

Lamak · Accepted Answer · 2013-03-21 18:11:03Z

8

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 guessed the columns that related your tables, but it should be very close to what you have, you need to use the real columns.

answered Mar 21, 2013 at 18:11

Lamak

70.8k12 gold badges119 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Richard Pearce Over a year ago

Saviour!! Works a treat now. Thanks!

Collectives™ on Stack Overflow

SQL Server Update Using Multiple Tables [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related