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

这是一个预订系统,所以它必须是原子和可靠的。如果事务已提交且机票已预订,则必须返回true。

我确定如何使用@@rowcount。这就是我到目前为止所写的。我走的路对吗?

-- 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?)

当前回答

解决这个问题的最佳方法是首先使数据库列UNIQUE

添加唯一键

THEN INSERT IGNORE INTO table_name,如果结果是一个重复的键或已经存在于表中,则该值不会被插入。

其他回答

INSERT INTO table ( column1, column2, column3 )
SELECT $column1, $column2, $column3
EXCEPT SELECT column1, column2, column3
FROM table

我在写我的解。我的方法不支持“if”或“merge”。我的方法很简单。

INSERT INTO TableName (col1,col2)
SELECT @par1, @par2
   WHERE NOT EXISTS (SELECT col1,col2 FROM TableName
                     WHERE col1=@par1 AND col2=@par2)

例如:

INSERT INTO Members (username)
SELECT 'Cem'
   WHERE NOT EXISTS (SELECT username FROM Members
                     WHERE username='Cem')

解释:

(1) SELECT col1,col2 FROM TableName WHERE col1=@par1 AND col2=@par2 它从TableName搜索值中选择

(2)选择不存在的@par1, @par2 它接受(1)子查询中if不存在

(3)在TableName(2)中插入步骤值

INSERT INTO [DatabaseName1].dbo.[TableName1] SELECT * FROM [DatabaseName2].dbo.[TableName2]
 WHERE [YourPK] not in (select [YourPK] from [DatabaseName1].dbo.[TableName1])

简单的方法将数据从T1复制到T2,避免在T2中重复

--Insert a new record
INSERT INTO dbo.Table2(NoEtu, FirstName, LastName)
SELECT t1.NoEtuDos, t1.FName, t1.LName 
FROM dbo.Table1 as t1
WHERE NOT EXISTS (SELECT (1) FROM dbo.Table2  AS t2
                    WHERE t1.FName = t2.FirstName
                        AND t1.LName = t2.LastName
                        AND t1.NoEtuDos = t2.NoEtu)

我假设每个航班都有一排座位?如果是这样:

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

我认为我说的,因为你的做事方式可能会超额预订航班,因为当最多有10张票而你预订了20张票时,它会插入一个新的行。