[sql] 행이 존재하는지 확인하고 그렇지 않으면 삽입

Program / 글쓴이

테이블의 행을 업데이트하는 T-SQL 저장 프로 시저를 작성해야합니다. 행이 존재하지 않으면 삽입하십시오. 이 모든 단계는 트랜잭션으로 래핑됩니다.

이것은 예약 시스템 용이므로 원자적이고 신뢰할 수 있어야합니다 . 트랜잭션이 커밋되고 항공편이 예약 된 경우 true를 반환해야합니다.

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

답변

MERGE 명령을 살펴보십시오 . 당신은 할 수있는 UPDATE, INSERT및 DELETE하나 개의 문장이다.

다음은 사용에 대한 실제 구현입니다 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

그리고 …

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

답변

각 항공편에 대해 단일 행을 가정합니까? 그렇다면:

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

최대 10 매의 티켓이 있고 20 명을 예약 할 때 새 행을 삽입하므로 항공편 예약을 초과 할 수 있기 때문에 내가 말한 것을 가정합니다.

답변

행의 존재를 테스트 할 때 업록, 행록, 홀드 락 힌트를 전달하십시오. 

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 */

updlock 힌트는 쿼리가 이미 존재하는 경우 쿼리에서 업데이트 잠금을 수행하도록하여 커밋 또는 롤백 할 때까지 다른 트랜잭션이 수정하지 못하도록합니다.

holdlock 힌트는 쿼리가 범위 잠금을 수행하도록하여 커밋 또는 롤백 할 때까지 다른 트랜잭션이 필터 기준과 일치하는 행을 추가하지 못하게합니다.

rowlock 힌트는 기본 페이지 수준이 아닌 행 단위로 잠금 세분성을 강제 적용하므로 트랜잭션은 동일한 페이지에서 관련없는 행을 업데이트하려고하는 다른 트랜잭션을 차단하지 않습니다 (그러나 경합 감소와 잠금 오버 헤드-단일 트랜잭션에서 많은 수의 행 수준 잠금을 사용하지 않아야합니다.

자세한 내용은 http://msdn.microsoft.com/en-us/library/ms187373.aspx 를 참조하십시오.

잠금은 그것들을 실행하는 문장으로 간주됩니다. begin tran을 호출한다고해서 다른 트랜잭션이 잠기기 전에 잠금을 잠그는 것을 막을 수는 없습니다. 가능한 한 빨리 트랜잭션을 커밋 (늦게 획득하고 일찍 릴리스)하여 가능한 가장 짧은 시간 동안 잠금을 유지하도록 SQL을 시도하고 인수 분해해야합니다.

SQL Server의 내부 해시가 64 비트 값으로 퇴화되기 때문에 PK가 큰 경우 행 수준 잠금이 덜 효과적 일 수 있습니다 (다른 키 값이 동일한 잠금 ID로 해시 될 수 있음).

답변

내 솔루션을 작성 중입니다. 내 방법은 ‘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) SELECT @ par1, @ par2 존재하지 않는 경우 (1) 하위 쿼리에서 존재하지 않는 경우 소요

(3) TableName에 삽입 (2) 단계 값

답변

다음 모델을 사용하여 이미 존재하지 않는 조건에서 행을 삽입 할 수있었습니다. 

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

답변

이것은 내가 최근에해야했던 일입니다.

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

답변

병합 기능을 사용하여 달성 할 수 있습니다. 그렇지 않으면 당신은 할 수 있습니다 :

declare @rowCount int

select @rowCount=@@RowCount

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