检查一行是否存在，否则插入 [英] Check if a row exists, otherwise insert

问题描述

我需要编写一个 T-SQL 存储过程来更新表中的一行.如果该行不存在，则插入它.所有这些步骤都包含在一个事务中.

I need to write a T-SQL stored procedure that updates a row in a table. If the row doesn't exist, insert it. All this steps wrapped by a transaction.

这是一个预订系统，所以它必须原子性和可靠.如果事务已提交且航班已预订，则必须返回 true.

This is for a booking system, so it must be atomic and reliable. It must return true if the transaction was committed and the flight booked.

我刚接触 T-SQL，不确定如何使用 @@rowcount.这是我迄今为止所写的.我在正确的道路上吗?我敢肯定，这对你来说是一个简单的问题.

I'm new to T-SQL, and not sure on how to use @@rowcount. This is what I've written until now. Am I on the right road? I'm sure is an easy problem for you.

-- BEGIN TRANSACTION (HOW TO DO?)

UPDATE Bookings
 SET TicketsBooked = TicketsBooked + @TicketsToBook
 WHERE FlightId = @Id AND TicketsMax < (TicketsBooked + @TicketsToBook)

-- Here I need to insert only if the row doesn't exists.
-- If the row exists but the condition TicketsMax is violated, I must not insert 
-- the row and return FALSE

IF @@ROWCOUNT = 0 
BEGIN

 INSERT INTO Bookings ... (omitted)

END

-- END TRANSACTION (HOW TO DO?)

-- Return TRUE (How to do?)

推荐答案

我假设每个航班只有一行?如果是这样:

I assume a single row for each flight? If so:

IF EXISTS (SELECT * FROM Bookings WHERE FLightID = @Id)
BEGIN
    --UPDATE HERE
END
ELSE
BEGIN
   -- INSERT HERE
END

我假设我说的是，因为您的处理方式可能会超额预订航班，因为当最多有 10 张机票并且您预订 20 张时，它会插入一个新行.

I assume what I said, as your way of doing things can overbook a flight, as it will insert a new row when there are 10 tickets max and you are booking 20.

这篇关于检查一行是否存在，否则插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！