رفع خطای MERGE SQL Server ( sql error 701 state 1 ) یا به‌روزرسانی یا حذف همزمان یک سطر

استیتمنت MERGE که برای اولین بار در SQL Server 2008 معرفی شد، ابزاری بسیار کارآمد برای همگام‌سازی داده‌ها بین دو جدول (منبع و مقصد) بر اساس یک کلید مشترک است. این دستور قدرتمند می‌تواند عملیات INSERT، UPDATE و DELETE را به ترتیب با استفاده از بندهای `WHEN MATCHED`, `WHEN NOT MATCHED BY TARGET` و `WHEN NOT MATCHED BY SOURCE` اجرا کند.

یکی از خطاهای رایج در هنگام استفاده از استیتمنت MERGE، خطای زیر است:

The MERGE statement attempted to UPDATE or DELETE the same row more than once.

این مشکل زمانی رخ می‌دهد که یک سطر از جدول مقصد با بیش از یک سطر از جدول منبع تطابق پیدا کند. جزئیات این خطا به شکل زیر است:

(SQL Error 701, State 1)

این مقاله به بررسی علت وقوع این خطا و ارائه راه‌حل‌های موثر برای رفع آن می‌پردازد.

**سناریوی ایجاد خطا**

برای درک بهتر این خطا، یک سناریوی نمونه را در نظر می‌گیریم. ابتدا دو جدول `EmployeeTarget` و `EmployeeSource` را ایجاد می‌کنیم.

-- حذف جداول در صورت وجود
IF OBJECT_ID('EmployeeTarget') IS NOT NULL DROP TABLE EmployeeTarget;
IF OBJECT_ID('EmployeeSource') IS NOT NULL DROP TABLE EmployeeSource;
GO

-- ایجاد جدول مقصد (EmployeeTarget)
CREATE TABLE EmployeeTarget
(
    EmployeeID INT NOT NULL PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DateOfBirth DATETIME
);
GO

-- وارد کردن داده‌های نمونه به جدول مقصد
INSERT INTO EmployeeTarget VALUES (1, 'Sachin', 'Tendulkar', '1973-04-24');
INSERT INTO EmployeeTarget VALUES (2, 'Rahul', 'Dravid', '1973-01-11');
INSERT INTO EmployeeTarget VALUES (3, 'Sourav', 'Ganguly', '1972-07-08');
INSERT INTO EmployeeTarget VALUES (4, 'VVS', 'Laxman', '1974-11-01');
GO

-- ایجاد جدول منبع (EmployeeSource)
CREATE TABLE EmployeeSource
(
    EmployeeID INT NOT NULL,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DateOfBirth DATETIME
);
GO

-- وارد کردن داده‌های نمونه به جدول منبع
INSERT INTO EmployeeSource VALUES (1, 'Sachin', 'Tendulkar', '1973-04-24'); -- یکسان با سطر 1 در مقصد
INSERT INTO EmployeeSource VALUES (2, 'Rahul', 'Dravid', '1973-01-11'); -- یکسان با سطر 2 در مقصد
INSERT INTO EmployeeSource VALUES (5, 'Virender', 'Sehwag', '1978-10-20'); -- سطر جدید برای درج
INSERT INTO EmployeeSource VALUES (6, 'Anil', 'Kumble', '1970-10-17'); -- سطر جدید برای درج
INSERT INTO EmployeeSource VALUES (1, 'Sachin', 'Ramesh', '1973-04-24'); -- سطر تکراری برای EmployeeID 1
GO

اکنون داده‌های جدول `EmployeeTarget` و `EmployeeSource` به شرح زیر است:

**EmployeeTarget:**

EmployeeID    FirstName    LastName    DateOfBirth
-----------   -----------  ----------- -----------
1             Sachin       Tendulkar   1973-04-24
2             Rahul        Dravid      1973-01-11
3             Sourav       Ganguly     1972-07-08
4             VVS          Laxman      1974-11-01

**EmployeeSource:**

EmployeeID    FirstName    LastName    DateOfBirth
-----------   -----------  ----------- -----------
1             Sachin       Tendulkar   1973-04-24
2             Rahul        Dravid      1973-01-11
5             Virender     Sehwag      1978-10-20
6             Anil         Kumble      1970-10-17
1             Sachin       Ramesh      1973-04-24

همانطور که مشاهده می‌کنید، در جدول `EmployeeSource` دو سطر با `EmployeeID = 1` وجود دارد. این موضوع باعث می‌شود که هنگام اجرای MERGE، سطر با `EmployeeID = 1` در `EmployeeTarget` با دو سطر در `EmployeeSource` تطابق پیدا کند، که منجر به خطای ذکر شده می‌شود.

حالا، سعی می‌کنیم استیتمنت MERGE را اجرا کنیم:

MERGE EmployeeTarget AS T
USING EmployeeSource AS S
ON T.EmployeeID = S.EmployeeID
WHEN MATCHED THEN
    UPDATE SET T.FirstName = S.FirstName,
               T.LastName = S.LastName,
               T.DateOfBirth = S.DateOfBirth
WHEN NOT MATCHED BY TARGET THEN
    INSERT (EmployeeID, FirstName, LastName, DateOfBirth)
    VALUES (S.EmployeeID, S.FirstName, S.LastName, S.DateOfBirth)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

اجرای این کد منجر به خطای زیر می‌شود:

Msg 21005, Level 16, State 1, Line 33
The MERGE statement attempted to UPDATE or DELETE the same row more than once.
This happens when a target row matches more than one source row.
To avoid this error, join the source table to a derived table that returns only one row per join key,
for example, by using the GROUP BY clause or by using the ROW_NUMBER() in a CTE.

این خطا به وضوح نشان می‌دهد که سطر مقصد با شناسه `EmployeeID = 1`، دو بار در جدول منبع ظاهر شده است. SQL Server نیاز دارد که هر سطر در جدول مقصد حداکثر با یک سطر در جدول منبع مطابقت داشته باشد تا عملیات `UPDATE` یا `DELETE` به درستی انجام شود.

**راه‌حل‌ها**

برای حل این مشکل، باید اطمینان حاصل کنیم که جدول منبع (Source) تنها شامل رکوردهای منحصر به فرد (Unique) بر اساس کلید Join است. در ادامه سه روش برای دستیابی به این هدف ارائه می‌شود.

**1. استفاده از GROUP BY:**

یکی از ساده‌ترین راه‌ها، استفاده از بند `GROUP BY` بر روی کلید مشترک در جدول منبع است. این کار تضمین می‌کند که برای هر `EmployeeID` تنها یک سطر به MERGE ارسال شود.

MERGE EmployeeTarget AS T
USING
(
    SELECT
        EmployeeID,
        MIN(FirstName) AS FirstName, -- استفاده از MIN یا MAX برای انتخاب یک مقدار از گروه‌های تکراری
        MIN(LastName) AS LastName,
        MIN(DateOfBirth) AS DateOfBirth
    FROM EmployeeSource
    GROUP BY EmployeeID
) AS S
ON T.EmployeeID = S.EmployeeID
WHEN MATCHED THEN
    UPDATE SET T.FirstName = S.FirstName,
               T.LastName = S.LastName,
               T.DateOfBirth = S.DateOfBirth
WHEN NOT MATCHED BY TARGET THEN
    INSERT (EmployeeID, FirstName, LastName, DateOfBirth)
    VALUES (S.EmployeeID, S.FirstName, S.LastName, S.DateOfBirth)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

-- بررسی نتایج
SELECT * FROM EmployeeTarget;

این روش مشکل را حل می‌کند، اما ممکن است همیشه رفتار مورد نظر ما را نداشته باشد، زیرا `MIN` یا `MAX` به صورت دلخواه یکی از مقادیر را برای ستون‌های غیرکلیدی انتخاب می‌کند. اگر نیاز به منطق پیچیده‌تری برای انتخاب سطر “درست” دارید، باید از روش‌های دیگر استفاده کنید.

**2. استفاده از DISTINCT:**

روش `DISTINCT` نیز می‌تواند برای حذف سطرهای تکراری بر اساس تمام ستون‌های انتخاب شده استفاده شود.

MERGE EmployeeTarget AS T
USING
(
    SELECT DISTINCT
        EmployeeID,
        FirstName,
        LastName,
        DateOfBirth
    FROM EmployeeSource
) AS S
ON T.EmployeeID = S.EmployeeID
WHEN MATCHED THEN
    UPDATE SET T.FirstName = S.FirstName,
               T.LastName = S.LastName,
               T.DateOfBirth = S.DateOfBirth
WHEN NOT MATCHED BY TARGET THEN
    INSERT (EmployeeID, FirstName, LastName, DateOfBirth)
    VALUES (S.EmployeeID, S.FirstName, S.LastName, S.DateOfBirth)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

-- بررسی نتایج
SELECT * FROM EmployeeTarget;

این روش تنها در صورتی کار می‌کند که کل سطرها (تمام ستون‌ها) تکراری باشند. اگر فقط `EmployeeID` تکراری باشد اما سایر ستون‌ها متفاوت باشند، `DISTINCT` ممکن است سطرها را حذف نکند. برای مثال در داده‌های ما، سطر اول `(1, ‘Sachin’, ‘Tendulkar’, ‘1973-04-24’)` و سطر پنجم `(1, ‘Sachin’, ‘Ramesh’, ‘1973-04-24’)` در `EmployeeSource` دقیقاً یکسان نیستند؛ `LastName` آن‌ها متفاوت است، بنابراین `DISTINCT` هیچ‌کدام را حذف نمی‌کند و خطا همچنان باقی می‌ماند.

**3. استفاده از Common Table Expression (CTE) و ROW_NUMBER(): (راه‌حل پیشنهادی و قدرتمندتر)**

این روش انعطاف‌پذیری بیشتری را برای انتخاب سطر مورد نظر در صورت وجود تکرار فراهم می‌کند. با استفاده از تابع پنجره‌ای `ROW_NUMBER()`، می‌توانیم برای هر گروه از `EmployeeID`های تکراری، یک شماره سطر منحصر به فرد اختصاص دهیم و سپس فقط سطر اول (`RowNumber = 1`) را انتخاب کنیم.

-- ابتدا داده‌ها را به حالت اولیه برمی‌گردانیم تا تأثیر راه‌حل را ببینیم
TRUNCATE TABLE EmployeeTarget;
INSERT INTO EmployeeTarget VALUES (1, 'Sachin', 'Tendulkar', '1973-04-24');
INSERT INTO EmployeeTarget VALUES (2, 'Rahul', 'Dravid', '1973-01-11');
INSERT INTO EmployeeTarget VALUES (3, 'Sourav', 'Ganguly', '1972-07-08');
INSERT INTO EmployeeTarget VALUES (4, 'VVS', 'Laxman', '1974-11-01');

-- MERGE با استفاده از CTE و ROW_NUMBER
MERGE EmployeeTarget AS T
USING
(
    SELECT
        EmployeeID,
        FirstName,
        LastName,
        DateOfBirth
    FROM
    (
        SELECT
            EmployeeID,
            FirstName,
            LastName,
            DateOfBirth,
            ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY (SELECT NULL)) AS RowNumber
        FROM EmployeeSource
    ) AS DedupeSource
    WHERE DedupeSource.RowNumber = 1
) AS S
ON T.EmployeeID = S.EmployeeID
WHEN MATCHED THEN
    UPDATE SET T.FirstName = S.FirstName,
               T.LastName = S.LastName,
               T.DateOfBirth = S.DateOfBirth
WHEN NOT MATCHED BY TARGET THEN
    INSERT (EmployeeID, FirstName, LastName, DateOfBirth)
    VALUES (S.EmployeeID, S.FirstName, S.LastName, S.DateOfBirth)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

-- بررسی نتایج
SELECT * FROM EmployeeTarget;

در این راه‌حل:
* `PARTITION BY EmployeeID` تضمین می‌کند که شماره‌گذاری سطرها برای هر `EmployeeID` از نو شروع می‌شود.
* `ORDER BY (SELECT NULL)` نشان‌دهنده عدم اهمیت ترتیب در انتخاب سطر اول است. اگر ترتیب خاصی برای انتخاب سطر “صحیح” بین تکراری‌ها دارید (مثلاً آخرین تاریخ به‌روزرسانی)، می‌توانید آن را جایگزین `(SELECT NULL)` کنید.
* `WHERE DedupeSource.RowNumber = 1` فقط اولین سطر از هر گروه `EmployeeID` را انتخاب می‌کند و تضمین می‌کند که داده‌های منبع برای MERGE منحصر به فرد هستند.

**مثال C# برای مدیریت خطای MERGE در SSIS (استفاده از Script Task)**

در محیط SQL Server Integration Services (SSIS)، اگر از Script Task برای اجرای MERGE استفاده می‌کنید، می‌توانید خطا را به صورت برنامه‌ریزی شده مدیریت کنید. فرض کنید یک `DataTable` به نام `dt` دارید که داده‌های منبع را شامل می‌شود و ممکن است دارای ردیف‌های تکراری باشد.

// using System.Data;
// using System.Linq; // برای استفاده از LINQ

public void Main()
{
    DataTable dt = new DataTable();
    dt.Columns.Add("EmployeeID", typeof(int));
    dt.Columns.Add("FirstName", typeof(string));
    dt.Columns.Add("LastName", typeof(string));
    dt.Columns.Add("DateOfBirth", typeof(DateTime));

    // اضافه کردن داده‌های نمونه (با تکرار)
    dt.Rows.Add(1, "Sachin", "Tendulkar", new DateTime(1973, 4, 24));
    dt.Rows.Add(2, "Rahul", "Dravid", new DateTime(1973, 1, 11));
    dt.Rows.Add(5, "Virender", "Sehwag", new DateTime(1978, 10, 20));
    dt.Rows.Add(6, "Anil", "Kumble", new DateTime(1970, 10, 17));
    dt.Rows.Add(1, "Sachin", "Ramesh", new DateTime(1973, 4, 24)); // سطر تکراری

    // حذف تکراری‌ها با LINQ - اینجا فرض می‌کنیم اولین سطر از گروه EmployeeID مد نظر است.
    DataTable distinctDt = dt.AsEnumerable()
                             .GroupBy(r => r.Field("EmployeeID"))
                             .Select(g => g.First())
                             .CopyToDataTable();

    // حالا distinctDt شامل ردیف‌های منحصر به فرد بر اساس EmployeeID است
    // می‌توانید از این DataTable برای ساخت SQL MERGE statement استفاده کنید
    // یا آن را به عنوان ورودی به یک Stored Procedure ارسال کنید که MERGE را انجام می‌دهد.

    // مثال: ساخت یک رشته SQL برای MERGE (این بخش نیاز به اتصال به دیتابیس دارد)
    // String sqlMergeStatement = "MERGE EmployeeTarget AS T USING ...";
    // اجرای sqlMergeStatement با استفاده از SqlCommand
    // ...
}

این مثال C# نشان می‌دهد که چگونه می‌توان داده‌های تکراری را قبل از اینکه به استیتمنت MERGE در SQL Server ارسال شوند، از طریق کد حذف کرد. این رویکرد به ویژه زمانی مفید است که کنترل بر روی داده‌های ورودی را قبل از عملیات MERGE داشته باشید. استفاده از `LINQ` با `GroupBy` و `Select(g => g.First())` یک راه کارآمد برای دستیابی به این هدف است.