SQL / C＃ - 上UPSERT主键错误 [英] SQL/C# - Primary Key error on UPSERT

问题描述

更新（适用简化的问题，从问题中删除C＃）

我如何写一个UPSERT当两行是在以下的情况相同，可以识别...

请参阅如何有一个 \b [退格键] 编码有（怪异的小角色）？ SQL认为这些一样。虽然我的UPSERT认为这是新的数据并尝试一个INSERT那里应该是UPDATE。

---

  // UPSERT 
 INSERT INTO [表] 
选择[COL1] = @ COL1，[COL2] = @ COL2，[COL3] = @ COL3， [COL4] = @ COL4 
 FROM [表] 
 WHERE NOT EXISTS 
  - 在这里的比赛条件下风险？ 
（SELECT 1 FROM [表] 
，其中
 [COL1] = @ COL1 
和[COL2 = @ COL2 
和[COL3] = @ COL3）
 
更新[表] 
设置[COL4] = @ COL4 
，其中
 [COL1] = @ COL1 
和[COL2 = @ COL2 
和[COL3] = @ COL3 
  

解决方案

在小时修修补补的事实证明我一直在白费力气。这个问题很简单。我把从流行的SO职位。中的代码也是白搭。选择有时会在INSERT返回> 1的行。因此试图插入行，然后插入同一行再次

解决方法是移除

  // UPSERT 
 INSERT INTO [表] 
选择[COL1] = @ COL1，[COL2] = @ COL2，[COL3] = @ COL3，[COL4] = @ COL4 
 --from [表]（不使用FROM..not竞争条件，只是一个坏SELECT）
 WHERE NOT EXISTS 
（SELECT 1 FROM [表] 
，其中
 [COL1] = @ COL1 
和[COL2 = @ COL2 
和[COL3] = @ COL3）
 
 UPDATE [表] 
设置[COL4] = @ COL4 
，其中
 [COL1] = @ COL1 
和[COL2 = @ COL2 
和[COL3] = @ COL3 
  

问题已经一去不复返了。

感谢大家。

UPDATE(simplified problem, removed C# from the issue)

How can I write an UPSERT that can recognize when two rows are the same in the following case...

See how there's a \b [backspace] encoded there (the weird little character)? SQL sees these as the same. While my UPSERT sees this as new data and attempts an INSERT where there should be an UPDATE.

---

//UPSERT
    INSERT INTO [table]
    SELECT [col1] = @col1, [col2] = @col2, [col3] = @col3, [col4] = @col4
    FROM [table]
    WHERE NOT EXISTS
       -- race condition risk here?
       ( SELECT 1 FROM [table] 
       WHERE 
            [col1] = @col1 
        AND [col2] = @col2
        AND [col3] = @col3)

    UPDATE [table]
        SET [col4] = @col4
        WHERE 
        [col1] = @col1 
        AND [col2] = @col2
        AND [col3] = @col3

解决方案

After hours of tinkering it turns out I've been on a wild goose chase. The problem is very simple. I pulled my UPSERT from a popular SO post. The code is no good. The select will sometimes return > 1 rows on INSERT. Thereby attempting to insert a row, then insert the same row again.

The fix is to remove FROM

    //UPSERT
    INSERT INTO [table]
    SELECT [col1] = @col1, [col2] = @col2, [col3] = @col3, [col4] = @col4
    --FROM [table] (Dont use FROM..not a race condition, just a bad SELECT)
    WHERE NOT EXISTS
       ( SELECT 1 FROM [table] 
       WHERE 
            [col1] = @col1 
        AND [col2] = @col2
        AND [col3] = @col3)

    UPDATE [table]
        SET [col4] = @col4
        WHERE 
        [col1] = @col1 
        AND [col2] = @col2
        AND [col3] = @col3

Problem is gone.

Thanks to all of you.

这篇关于SQL / C＃ - 上UPSERT主键错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！