dimanche 25 février 2018

T-SQL If Statement issue

I have 2 datatables dbo.Videos and dbo.Checkouts. The dbo.Videos datatable contains a list of videos while the dbo.Checkouts is datatable keeping track of the videos that have been checked out. The goal in my tsql command is to insert a new row in the dbo.Checkouts table including VideoId, UserId, CheckoutDate. Once this is successful I then want to update the dbo.Videos and decrement the TotalCopies column value based on the VideoID selected only if the value is greater than 0. If less than 0 I want to throw an exception. The VideoID in both datatables are linked by foreignkey. However, the IF statement I have included in my statement below throws an error. How would I fix this. Thanks in advance.

INSERT INTO dbo.Checkouts (VideoId, UserId, CheckoutDate)
VALUES (32, 'b0281f0d-8398-4a27-ba92-828bfaa9f90e', CURRENT_TIMESTAMP)

IF (SELECT TotalCopies FROM dbo.Videos WHERE VideoId = 32) > 0
UPDATE dbo.Videos SET
TotalCopies = TotalCopies - 1
WHERE VideoID = 32

Aucun commentaire:

Enregistrer un commentaire