SQL 检查一行是否存在，否则插入

Question

提问by

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.

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

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.

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

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.

我是T-SQL 的新手，不确定如何使用@@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?)

Answer 1

回答by dance2die

Take a look at MERGEcommand. You can do UPDATE, INSERT& DELETEin one statement.

看看MERGE命令。您可以在一个语句中执行UPDATE, INSERT& DELETE。

Here is a working implementation on using MERGE
- It checks whether flight is full before doing an update, else does an insert.

这是一个关于 using 的工作实现MERGE
- 它在进行更新之前检查航班是否已满，否则进行插入。

if exists(select 1 from INFORMATION_SCHEMA.TABLES T 
              where T.TABLE_NAME = 'Bookings') 
begin
    drop table Bookings
end
GO

create table Bookings(
  FlightID    int identity(1, 1) primary key,
  TicketsMax    int not null,
  TicketsBooked int not null
)
GO

insert  Bookings(TicketsMax, TicketsBooked) select 1, 0
insert  Bookings(TicketsMax, TicketsBooked) select 2, 2
insert  Bookings(TicketsMax, TicketsBooked) select 3, 1
GO

select * from Bookings

And then ...

进而 ...

declare @FlightID int = 1
declare @TicketsToBook int = 2

--; This should add a new record
merge Bookings as T
using (select @FlightID as FlightID, @TicketsToBook as TicketsToBook) as S
    on  T.FlightID = S.FlightID
      and T.TicketsMax > (T.TicketsBooked + S.TicketsToBook)
  when matched then
    update set T.TicketsBooked = T.TicketsBooked + S.TicketsToBook
  when not matched then
    insert (TicketsMax, TicketsBooked) 
    values(S.TicketsToBook, S.TicketsToBook);

select * from Bookings

Answer 2

回答by Gregory A Beamer

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

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.

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

Answer 3

回答by Cassius Porcus

Pass updlock, rowlock, holdlock hints when testing for existence of the row.

在测试行是否存在时传递 updlock、rowlock、holdlock 提示。

begin tran /* default read committed isolation level is fine */

if not exists (select * from Table with (updlock, rowlock, holdlock) where ...)
    /* insert */
else
    /* update */

commit /* locks are released here */

The updlock hint forces the query to take an update lock on the row if it already exists, preventing other transactions from modifying it until you commit or roll back.

updlock 提示强制查询在行上获取更新锁（如果它已经存在），防止其他事务修改它，直到您提交或回滚。

The holdlock hint forces the query to take a range lock, preventing other transactions from adding a row matching your filter criteria until you commit or roll back.

holdlock 提示强制查询采用范围锁定，防止其他事务添加与您的过滤条件匹配的行，直到您提交或回滚。

The rowlock hint forces lock granularity to row level instead of the default page level, so your transaction won't block other transactions trying to update unrelated rows in the same page (but be aware of the trade-off between reduced contention and the increase in locking overhead - you should avoid taking large numbers of row-level locks in a single transaction).

rowlock 提示将锁粒度强制为行级别而不是默认页面级别，因此您的事务不会阻止其他事务尝试更新同一页面中不相关的行（但请注意减少争用和增加锁定开销 - 您应该避免在单个事务中使用大量行级锁）。

See http://msdn.microsoft.com/en-us/library/ms187373.aspxfor more information.

有关详细信息，请参阅http://msdn.microsoft.com/en-us/library/ms187373.aspx。

Note that locks are taken as the statements which take them are executed - invoking begin tran doesn't give you immunity against another transaction pinching locks on something before you get to it. You should try and factor your SQL to hold locks for the shortest possible time by committing the transaction as soon as possible (acquire late, release early).

请注意，锁定是在执行获取它们的语句时获取的 - 调用 begin tran 并不能让您免受另一个事务在您到达之前锁定某事的免疫力。您应该尝试通过尽快提交事务（延迟获取，提前释放）来尝试将您的 SQL 分解为在尽可能短的时间内保持锁定。

Note that row-level locks may be less effective if your PK is a bigint, as the internal hashing on SQL Server is degenerate for 64-bit values (different key values may hash to the same lock id).

请注意，如果您的 PK 是 bigint，则行级锁可能不太有效，因为 SQL Server 上的内部散列对于 64 位值是退化的（不同的键值可能散列到相同的锁 ID）。

Answer 4

回答by Cem

i'm writing my solution. my method doesn't stand 'if' or 'merge'. my method is easy.

我正在写我的解决方案。我的方法不支持“如果”或“合并”。我的方法很简单。

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

For Example:

例如：

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

Explanation:

解释：

(1) SELECT col1,col2 FROM TableName WHERE col1=@par1 AND col2=@par2 It selects from TableName searched values

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

(2) SELECT @par1, @par2 WHERE NOT EXISTS It takes if not exists from (1) subquery

(2) SELECT @par1, @par2 WHERE NOT EXISTS 如果不存在则从 (1) 子查询中获取

(3) Inserts into TableName (2) step values

(3) 插入到 TableName (2) 步长值

Answer 5

回答by Paul G

I finally was able to insert a row, on the condition that it didn't already exist, using the following model:

我终于能够插入一行，条件是它不存在，使用以下模型：

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

which I found at:

我在以下位置找到的：

http://www.postgresql.org/message-id/[email protected]

Answer 6

回答by TheTXI

This is something I just recently had to do:

这是我最近不得不做的事情：

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[cjso_UpdateCustomerLogin]
    (
      @CustomerID AS INT,
      @UserName AS VARCHAR(25),
      @Password AS BINARY(16)
    )
AS 
    BEGIN
        IF ISNULL((SELECT CustomerID FROM tblOnline_CustomerAccount WHERE CustomerID = @CustomerID), 0) = 0
        BEGIN
            INSERT INTO [tblOnline_CustomerAccount] (
                [CustomerID],
                [UserName],
                [Password],
                [LastLogin]
            ) VALUES ( 
                /* CustomerID - int */ @CustomerID,
                /* UserName - varchar(25) */ @UserName,
                /* Password - binary(16) */ @Password,
                /* LastLogin - datetime */ NULL ) 
        END
        ELSE
        BEGIN
            UPDATE  [tblOnline_CustomerAccount]
            SET     UserName = @UserName,
                    Password = @Password
            WHERE   CustomerID = @CustomerID    
        END

    END

Answer 7

回答by JoshBerke

You could use the MergeFunctionality to achieve. Otherwise you can do:

您可以使用合并功能来实现。否则你可以这样做：

declare @rowCount int

select @rowCount=@@RowCount

if @rowCount=0
begin
--insert....

Answer 8

回答by user2836818

Full solution is below (including cursor structure). Many thanks to Cassius Porcus for the begin trans ... commitcode from posting above.

完整的解决方案如下（包括游标结构）。非常感谢 Cassius Porcusbegin trans ... commit从上面发布的代码。

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

Answer 9

回答by Almamun

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

Answer 10

回答by Maurice Elagu

The best approach to this problem is first making the database column UNIQUE

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

ALTER TABLE table_name ADD UNIQUE KEY

THEN INSERT IGNORE INTO table_name,the value won't be inserted if it results in a duplicate key/already exists in the table.

THEN INSERT IGNORE INTO table_name,如果该值导致重复键/表中已存在，则不会插入该值。

SQL 检查一行是否存在，否则插入

提问by

回答by dance2die

回答by Gregory A Beamer

回答by Cassius Porcus

回答by Cem

回答by Paul G

回答by TheTXI

回答by JoshBerke

回答by user2836818

回答by Almamun

回答by Maurice Elagu

相关推荐

最近更新

标签

SQL 检查一行是否存在，否则插入

提问by

回答by dance2die

回答by Gregory A Beamer

回答by Cassius Porcus

回答by Cem

回答by Paul G

回答by TheTXI

回答by JoshBerke

回答by user2836818

回答by Almamun

回答by Maurice Elagu

相关推荐

为什么要大写我的 SQL 关键字？

如何在 SQL Server 中声明全局变量..？

SQL Server 中的覆盖索引和覆盖查询是什么？

有没有办法在 SQL 语句中将 IN 与 LIKE 结合使用？

相关推荐

最近更新

标签