我需要编写一个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?)
我最终能够插入一行，在它不存在的情况下，使用以下模型:
INSERT INTO table ( column1, column2, column3 )
(
SELECT $column1, $column2, $column3
WHERE NOT EXISTS (
SELECT 1
FROM table
WHERE column1 = $column1
AND column2 = $column2
AND column3 = $column3
)
)
我在:
http://www.postgresql.org/message-id/87hdow4ld1.fsf@stark.xeocode.com
我最终能够插入一行，在它不存在的情况下，使用以下模型:
INSERT INTO table ( column1, column2, column3 )
(
SELECT $column1, $column2, $column3
WHERE NOT EXISTS (
SELECT 1
FROM table
WHERE column1 = $column1
AND column2 = $column2
AND column3 = $column3
)
)
我在:
http://www.postgresql.org/message-id/87hdow4ld1.fsf@stark.xeocode.com
我在写我的解。我的方法不支持“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)中插入步骤值
完整解决方案如下(包括游标结构)。非常感谢卡修斯·波库斯开始翻译…提交上面发布的代码。
declare @mystat6 bigint
declare @mystat6p varchar(50)
declare @mystat6b bigint
DECLARE mycur1 CURSOR for
select result1,picture,bittot from all_Tempnogos2results11
OPEN mycur1
FETCH NEXT FROM mycur1 INTO @mystat6, @mystat6p , @mystat6b
WHILE @@Fetch_Status = 0
BEGIN
begin tran /* default read committed isolation level is fine */
if not exists (select * from all_Tempnogos2results11_uniq with (updlock, rowlock, holdlock)
where all_Tempnogos2results11_uniq.result1 = @mystat6
and all_Tempnogos2results11_uniq.bittot = @mystat6b )
insert all_Tempnogos2results11_uniq values (@mystat6 , @mystat6p , @mystat6b)
--else
-- /* update */
commit /* locks are released here */
FETCH NEXT FROM mycur1 INTO @mystat6 , @mystat6p , @mystat6b
END
CLOSE mycur1
DEALLOCATE mycur1
go
