SQL/C# - UPSERT 上的主键错误

发布于 2024-12-02 15:27:07 字数 750 浏览 1 评论 0原文

更新（简化问题，从问题中删除 C#）

在以下情况下，如何编写可以识别两行相同的 UPSERT...

SSMS

看看那里是如何编码\b [退格]（奇怪的小字符）的吗？ SQL 将它们视为相同。虽然我的 UPSERT 将此视为新数据，并尝试在应该有更新的地方插入。

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

原文

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

SSMS

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

拥抱没勇气 2024-12-09 15:27:07

您需要 @ 符号，否则会遇到 C# 字符转义序列。

C# 定义了以下字符转义序列：

\' - single quote, needed for character literals 
\" - double quote, needed for string literals 
\\ - backslash 
\0 - Unicode character 0 
\a - Alert (character 7) 
\b - Backspace (character 8) 
\f - Form feed (character 12) 
\n - New line (character 10) 
\r - Carriage return (character 13) 
\t - Horizontal tab (character 9) 
\v - Vertical quote (character 11) 
\uxxxx - Unicode escape sequence for character with hex value xxxx 
\xn[n][n][n] - Unicode escape sequence for character with hex value nnnn (variable length version of \uxxxx) 
\Uxxxxxxxx - Unicode escape sequence for character with hex value xxxxxxxx (for generating surrogates)

You need the @ sign, otherwise a C# character escape sequence is hit.

C# defines the following character escape sequences:

\' - single quote, needed for character literals 
\" - double quote, needed for string literals 
\\ - backslash 
\0 - Unicode character 0 
\a - Alert (character 7) 
\b - Backspace (character 8) 
\f - Form feed (character 12) 
\n - New line (character 10) 
\r - Carriage return (character 13) 
\t - Horizontal tab (character 9) 
\v - Vertical quote (character 11) 
\uxxxx - Unicode escape sequence for character with hex value xxxx 
\xn[n][n][n] - Unicode escape sequence for character with hex value nnnn (variable length version of \uxxxx) 
\Uxxxxxxxx - Unicode escape sequence for character with hex value xxxxxxxx (for generating surrogates)

回复收藏 0 原文

浅笑依然 2024-12-09 15:27:07

经过几个小时的修修补补，事实证明我一直在白费力气。问题很简单。我从一篇流行的SO帖子中提取了我的UPSERT。不行。 select 有时会返回 > INSERT 上有 1 行。从而尝试插入一行，然后再次插入同一行。

修复方法是删除 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

问题就消失了。

谢谢大家。

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.

回复收藏 0 原文