FROM 또는 하위 쿼리를 사용하여 UPDATE 구현Implementing UPDATE with FROM or Subqueries

이 항목은 다음에 적용됩니다.예SQL Server(2014부터)예Azure SQL Database아니요Azure SQL Data Warehouse 아니요병렬 데이터 웨어하우스 THIS TOPIC APPLIES TO:yesSQL Server (starting with 2014)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

고유하게 컴파일된 T-SQL 모듈은 FROM 절을 지원하지 않으며 UPDATE 문에서 하위 쿼리를 지원하지 않습니다(SELECT에서 지원됨).Natively compiled T-SQL modules do not support the FROM clause and do not support subqueries in UPDATE statements (they are supported in SELECT). FROM 절이 있는 UPDATE 문은 일반적으로 TVP(테이블 반환 매개 변수)에 따라 테이블의 정보를 업데이트하거나 AFTER 트리거에서 테이블의 열을 업데이트하는 데 사용됩니다.UPDATE statements with FROM clause are typically used to update information in a table based on a table-valued parameter (TVP), or to update columns in a table in an AFTER trigger.

TVP에 따라 업데이트하는 시나리오의 경우 고유하게 컴파일된 저장 프로시저에서 MERGE 기능 구현을 참조하세요.For the scenario of update based on a TVP, see Implementing MERGE Functionality in a Natively Compiled Stored Procedure.

아래 샘플에서는 트리거에서 수행되는 업데이트를 보여 줍니다. 테이블의 LastUpdated 열이 현재 날짜/시간 AFTER 업데이트로 업데이트됩니다.The below sample illustrates an update performed in a trigger - the LastUpdated column of the table is updated to the current date/time AFTER updates. 해결 방법은 ID 열과 함께 테이블 변수를 사용하고 WHILE 루프를 통해 테이블 변수에서 행을 반복하여 개별 업데이트를 수행하는 것입니다.The workaround uses a table variable with an identity column, and a WHILE loop to iterate of the rows in the table variable and perform individual updates.

원래 T-SQL UPDATE 문은 다음과 같습니다.Here is the original T-SQL UPDATE statement :

UPDATE dbo.Table1  
    SET LastUpdated = SysDateTime()  
    FROM  
        dbo.Table1 t  
        JOIN Inserted i ON t.Id = i.Id;  

이 섹션의 샘플 T-SQL 코드는 좋은 성능을 제공하는 해결 방법을 보여 줍니다.The sample T-SQL code in this section demonstrates a workaround that provides good performance. 해결 방법은 고유하게 컴파일된 트리거에서 구현되었습니다.The workaround is implemented in a natively compiled trigger. 코드에서 주의해야 할 사항은 다음과 같습니다.Crucial to notice in the code are:

  • 메모리 액세스에 최적화된 테이블 형식인 dbo.Type1 형식The type named dbo.Type1, which is a memory-optimized table type.
  • 트리거의 WHILE 루프.The WHILE loop in the trigger.
    • 루프는 Inserted에서 행을 한 번에 하나씩 검색합니다.The loop retrieves the rows from Inserted one at a time.
<span data-ttu-id="e5898-115">DROP TABLE IF EXISTS dbo.Table1;</span><span class="sxs-lookup"><span data-stu-id="e5898-115">DROP TABLE IF EXISTS dbo.Table1;</span></span>  
<span data-ttu-id="e5898-116">go</span><span class="sxs-lookup"><span data-stu-id="e5898-116">go</span></span>  
<span data-ttu-id="e5898-117">DROP TYPE IF EXISTS dbo.Type1;</span><span class="sxs-lookup"><span data-stu-id="e5898-117">DROP TYPE IF EXISTS dbo.Type1;</span></span>  
<span data-ttu-id="e5898-118">go</span><span class="sxs-lookup"><span data-stu-id="e5898-118">go</span></span>  
-----------------------------  
<a name="---table-and-table-type"></a><span data-ttu-id="e5898-119">-- Table and table type</span><span class="sxs-lookup"><span data-stu-id="e5898-119">-- Table and table type</span></span>
-----------------------------

<span data-ttu-id="e5898-120">CREATE TABLE dbo.Table1</span><span class="sxs-lookup"><span data-stu-id="e5898-120">CREATE TABLE dbo.Table1</span></span>  
<span data-ttu-id="e5898-121">(</span><span class="sxs-lookup"><span data-stu-id="e5898-121">(</span></span>  
    <span data-ttu-id="e5898-122">Id           INT        NOT NULL  PRIMARY KEY NONCLUSTERED,</span><span class="sxs-lookup"><span data-stu-id="e5898-122">Id           INT        NOT NULL  PRIMARY KEY NONCLUSTERED,</span></span>  
    <span data-ttu-id="e5898-123">Column2      INT        NOT NULL,</span><span class="sxs-lookup"><span data-stu-id="e5898-123">Column2      INT        NOT NULL,</span></span>  
    <span data-ttu-id="e5898-124">LastUpdated  DATETIME2  NOT NULL  DEFAULT (SYSDATETIME())</span><span class="sxs-lookup"><span data-stu-id="e5898-124">LastUpdated  DATETIME2  NOT NULL  DEFAULT (SYSDATETIME())</span></span>  
<span data-ttu-id="e5898-125">).</span><span class="sxs-lookup"><span data-stu-id="e5898-125">)</span></span>  
    <span data-ttu-id="e5898-126">WITH (MEMORY_OPTIMIZED = ON);</span><span class="sxs-lookup"><span data-stu-id="e5898-126">WITH (MEMORY_OPTIMIZED = ON);</span></span>  
<span data-ttu-id="e5898-127">go</span><span class="sxs-lookup"><span data-stu-id="e5898-127">go</span></span>  


<span data-ttu-id="e5898-128">CREATE TYPE dbo.Type1 AS TABLE</span><span class="sxs-lookup"><span data-stu-id="e5898-128">CREATE TYPE dbo.Type1 AS TABLE</span></span>  
<span data-ttu-id="e5898-129">(</span><span class="sxs-lookup"><span data-stu-id="e5898-129">(</span></span>  
    <span data-ttu-id="e5898-130">Id       INT NOT  NULL,</span><span class="sxs-lookup"><span data-stu-id="e5898-130">Id       INT NOT  NULL,</span></span>  

    RowID    INT NOT  NULL  IDENTITY,  
    INDEX ix_RowID HASH (RowID) WITH (BUCKET_COUNT=1024)
<span data-ttu-id="e5898-131">).</span><span class="sxs-lookup"><span data-stu-id="e5898-131">)</span></span>   
    <span data-ttu-id="e5898-132">WITH (MEMORY_OPTIMIZED = ON);</span><span class="sxs-lookup"><span data-stu-id="e5898-132">WITH (MEMORY_OPTIMIZED = ON);</span></span>  
<span data-ttu-id="e5898-133">go</span><span class="sxs-lookup"><span data-stu-id="e5898-133">go</span></span>  
----------------------------- 
<a name="---trigger-that-contains-the-workaround-for-update-with-from"></a><span data-ttu-id="e5898-134">-- trigger that contains the workaround for UPDATE with FROM</span><span class="sxs-lookup"><span data-stu-id="e5898-134">-- trigger that contains the workaround for UPDATE with FROM</span></span> 
-----------------------------  

<span data-ttu-id="e5898-135">CREATE TRIGGER dbo.tr_a_u_Table1</span><span class="sxs-lookup"><span data-stu-id="e5898-135">CREATE TRIGGER dbo.tr_a_u_Table1</span></span>  
    <span data-ttu-id="e5898-136">ON dbo.Table1</span><span class="sxs-lookup"><span data-stu-id="e5898-136">ON dbo.Table1</span></span>  
    <span data-ttu-id="e5898-137">WITH NATIVE_COMPILATION, SCHEMABINDING</span><span class="sxs-lookup"><span data-stu-id="e5898-137">WITH NATIVE_COMPILATION, SCHEMABINDING</span></span>  
    <span data-ttu-id="e5898-138">AFTER UPDATE</span><span class="sxs-lookup"><span data-stu-id="e5898-138">AFTER UPDATE</span></span>  
<span data-ttu-id="e5898-139">AS BEGIN ATOMIC WITH</span><span class="sxs-lookup"><span data-stu-id="e5898-139">AS BEGIN ATOMIC WITH</span></span>  
    <span data-ttu-id="e5898-140">(</span><span class="sxs-lookup"><span data-stu-id="e5898-140">(</span></span>  
    <span data-ttu-id="e5898-141">TRANSACTION ISOLATION LEVEL = SNAPSHOT,</span><span class="sxs-lookup"><span data-stu-id="e5898-141">TRANSACTION ISOLATION LEVEL = SNAPSHOT,</span></span>  
    <span data-ttu-id="e5898-142">LANGUAGE = N'us_english'</span><span class="sxs-lookup"><span data-stu-id="e5898-142">LANGUAGE = N'us_english'</span></span>  
    <span data-ttu-id="e5898-143">).</span><span class="sxs-lookup"><span data-stu-id="e5898-143">)</span></span>  

  <span data-ttu-id="e5898-144">DECLARE @tabvar1 dbo.Type1;</span><span class="sxs-lookup"><span data-stu-id="e5898-144">DECLARE @tabvar1 dbo.Type1;</span></span>  

  <span data-ttu-id="e5898-145">INSERT @tabvar1 (Id)</span><span class="sxs-lookup"><span data-stu-id="e5898-145">INSERT @tabvar1 (Id)</span></span>   
      <span data-ttu-id="e5898-146">SELECT Id FROM Inserted;</span><span class="sxs-lookup"><span data-stu-id="e5898-146">SELECT Id FROM Inserted;</span></span>  

  <span data-ttu-id="e5898-147">DECLARE</span><span class="sxs-lookup"><span data-stu-id="e5898-147">DECLARE</span></span>  
      <span data-ttu-id="e5898-148">@i INT = 1,  @Id INT,</span><span class="sxs-lookup"><span data-stu-id="e5898-148">@i INT = 1,  @Id INT,</span></span>  
      <span data-ttu-id="e5898-149">@max INT = SCOPE_IDENTITY();</span><span class="sxs-lookup"><span data-stu-id="e5898-149">@max INT = SCOPE_IDENTITY();</span></span>  

  <span data-ttu-id="e5898-150">---- Loop as a workaround to simulate a cursor.</span><span class="sxs-lookup"><span data-stu-id="e5898-150">---- Loop as a workaround to simulate a cursor.</span></span>
<span data-ttu-id="e5898-151">---- Iterate over the rows in the memory-optimized table</span><span class="sxs-lookup"><span data-stu-id="e5898-151">---- Iterate over the rows in the memory-optimized table</span></span>  
  <span data-ttu-id="e5898-152">----   variable and perform an update for each row.</span><span class="sxs-lookup"><span data-stu-id="e5898-152">----   variable and perform an update for each row.</span></span>  

  <span data-ttu-id="e5898-153">WHILE @i <= @max</span><span class="sxs-lookup"><span data-stu-id="e5898-153">WHILE @i <= @max</span></span>  
  <span data-ttu-id="e5898-154">BEGIN</span><span class="sxs-lookup"><span data-stu-id="e5898-154">BEGIN</span></span>  
      <span data-ttu-id="e5898-155">SELECT @Id = Id</span><span class="sxs-lookup"><span data-stu-id="e5898-155">SELECT @Id = Id</span></span>  
          <span data-ttu-id="e5898-156">FROM @tabvar1</span><span class="sxs-lookup"><span data-stu-id="e5898-156">FROM @tabvar1</span></span>  
          <span data-ttu-id="e5898-157">WHERE RowID = @i;</span><span class="sxs-lookup"><span data-stu-id="e5898-157">WHERE RowID = @i;</span></span>  

      UPDATE dbo.Table1  
          SET LastUpdated = SysDateTime()  
          WHERE Id = @Id;  

      SET @i += 1;  
  <span data-ttu-id="e5898-158">END</span><span class="sxs-lookup"><span data-stu-id="e5898-158">END</span></span>  
<span data-ttu-id="e5898-159">END</span><span class="sxs-lookup"><span data-stu-id="e5898-159">END</span></span>  
<span data-ttu-id="e5898-160">go</span><span class="sxs-lookup"><span data-stu-id="e5898-160">go</span></span>  
-----------------------------  
<a name="---test-to-verify-functionality"></a><span data-ttu-id="e5898-161">-- Test to verify functionality</span><span class="sxs-lookup"><span data-stu-id="e5898-161">-- Test to verify functionality</span></span>
-----------------------------  

<span data-ttu-id="e5898-162">SET NOCOUNT ON;</span><span class="sxs-lookup"><span data-stu-id="e5898-162">SET NOCOUNT ON;</span></span>  

<span data-ttu-id="e5898-163">INSERT dbo.Table1 (Id, Column2)</span><span class="sxs-lookup"><span data-stu-id="e5898-163">INSERT dbo.Table1 (Id, Column2)</span></span>  
    <span data-ttu-id="e5898-164">VALUES (1,9), (2,9), (3,600);</span><span class="sxs-lookup"><span data-stu-id="e5898-164">VALUES (1,9), (2,9), (3,600);</span></span>  

<span data-ttu-id="e5898-165">SELECT N'BEFORE-Update' AS [BEFORE-Update], *</span><span class="sxs-lookup"><span data-stu-id="e5898-165">SELECT N'BEFORE-Update' AS [BEFORE-Update], *</span></span>  
    <span data-ttu-id="e5898-166">FROM dbo.Table1</span><span class="sxs-lookup"><span data-stu-id="e5898-166">FROM dbo.Table1</span></span>  
    <span data-ttu-id="e5898-167">ORDER BY Id;</span><span class="sxs-lookup"><span data-stu-id="e5898-167">ORDER BY Id;</span></span>  

<span data-ttu-id="e5898-168">WAITFOR DELAY '00:00:01';</span><span class="sxs-lookup"><span data-stu-id="e5898-168">WAITFOR DELAY '00:00:01';</span></span>  

<span data-ttu-id="e5898-169">UPDATE dbo.Table1</span><span class="sxs-lookup"><span data-stu-id="e5898-169">UPDATE dbo.Table1</span></span>  
    <span data-ttu-id="e5898-170">SET   Column2 += 1</span><span class="sxs-lookup"><span data-stu-id="e5898-170">SET   Column2 += 1</span></span>  
    <span data-ttu-id="e5898-171">WHERE Column2 <= 99;</span><span class="sxs-lookup"><span data-stu-id="e5898-171">WHERE Column2 <= 99;</span></span>  

<span data-ttu-id="e5898-172">SELECT N'AFTER--Update' AS [AFTER--Update], *</span><span class="sxs-lookup"><span data-stu-id="e5898-172">SELECT N'AFTER--Update' AS [AFTER--Update], *</span></span>  
    <span data-ttu-id="e5898-173">FROM dbo.Table1</span><span class="sxs-lookup"><span data-stu-id="e5898-173">FROM dbo.Table1</span></span>  
    <span data-ttu-id="e5898-174">ORDER BY Id;</span><span class="sxs-lookup"><span data-stu-id="e5898-174">ORDER BY Id;</span></span>  
<span data-ttu-id="e5898-175">go</span><span class="sxs-lookup"><span data-stu-id="e5898-175">go</span></span>  
-----------------------------  

<span data-ttu-id="e5898-176">/**** Actual output:</span><span class="sxs-lookup"><span data-stu-id="e5898-176">/**** Actual output:</span></span>  

<span data-ttu-id="e5898-177">BEFORE-Update   Id   Column2   LastUpdated</span><span class="sxs-lookup"><span data-stu-id="e5898-177">BEFORE-Update   Id   Column2   LastUpdated</span></span>  
<span data-ttu-id="e5898-178">BEFORE-Update   1       9      2016-04-20 21:18:42.8394659</span><span class="sxs-lookup"><span data-stu-id="e5898-178">BEFORE-Update   1       9      2016-04-20 21:18:42.8394659</span></span>  
<span data-ttu-id="e5898-179">BEFORE-Update   2       9      2016-04-20 21:18:42.8394659</span><span class="sxs-lookup"><span data-stu-id="e5898-179">BEFORE-Update   2       9      2016-04-20 21:18:42.8394659</span></span>  
<span data-ttu-id="e5898-180">BEFORE-Update   3     600      2016-04-20 21:18:42.8394659</span><span class="sxs-lookup"><span data-stu-id="e5898-180">BEFORE-Update   3     600      2016-04-20 21:18:42.8394659</span></span>  

<span data-ttu-id="e5898-181">AFTER--Update   Id   Column2   LastUpdated</span><span class="sxs-lookup"><span data-stu-id="e5898-181">AFTER--Update   Id   Column2   LastUpdated</span></span>  
<span data-ttu-id="e5898-182">AFTER--Update   1      10      2016-04-20 21:18:43.8529692</span><span class="sxs-lookup"><span data-stu-id="e5898-182">AFTER--Update   1      10      2016-04-20 21:18:43.8529692</span></span>  
<span data-ttu-id="e5898-183">AFTER--Update   2      10      2016-04-20 21:18:43.8529692</span><span class="sxs-lookup"><span data-stu-id="e5898-183">AFTER--Update   2      10      2016-04-20 21:18:43.8529692</span></span>  
<span data-ttu-id="e5898-184">AFTER--Update   3     600      2016-04-20 21:18:42.8394659</span><span class="sxs-lookup"><span data-stu-id="e5898-184">AFTER--Update   3     600      2016-04-20 21:18:42.8394659</span></span>  
****/