CODEWITHSUNDEEP
sqlsql-server-2008-r2
jaypabs
jaypabs

Reputation: 1567

How to use Update Statement in SQL Server with join

The code below works fine with MS Access but I cannot make it work with SQL Server 2008.

UPDATE Borrow INNER JOIN BorrowDetails 
ON Borrow.BorrowID = BorrowDetails.BorrowID 
SET BorrowDetails.Returned = True 
WHERE BookID = 1 AND MemberID = 1

Any idea please.

Upvotes: 1

Views: 4869

Answers (3)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79949

Try this instead:

UPDATE bd
SET bd.Returned = 1
FROM BorrowDetails bd
INNER JOIN Borrow b ON bd.BorrowID = b.BorrowID    
WHERE b.BookID = 1 AND b.MemberID = 1

Upvotes: 1

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

UPDATE x
SET x.Returned = True 
FROM (
      SELECT Returned 
      FROM Borrow INNER JOIN BorrowDetails ON Borrow.BorrowID = BorrowDetails.BorrowID 
      WHERE BookID = 1 AND MemberID = 1
      ) x

Upvotes: 0

shahkalpesh
shahkalpesh

Reputation: 33474

UPDATE BorrowDetails
SET BorrowDetails.Returned = True 
FROM BorrowDetails
INNER JOIN Borrow ON Borrow.BorrowID = BorrowDetails.BorrowID 
WHERE BookID = 1 AND MemberID = 1

Upvotes: 2

Related Questions