sql - 可以使用 SQL Server 模拟 ON DUPLICATE KEY UPDATE 的 mySQL 功能

Question

我正在使用 SQL Server 2008，并且希望能够利用 mySQL 的 ON DUPLICATE KEY UPDATE 子句等 INSERT 语句

当前遗留代码执行删除和后续插入操作，但由于来自不同线程的重复键插入而遇到并发问题:

这是我在生产环境中看到的错误:

Violation of PRIMARY KEY constraint 'PK_Audience'. Cannot insert duplicate key in object 'dbo.Audience'.

(sp_ContentUpdate)

主键:

AudienceId, VersionId

违规 SQL:

DELETE  FROM  dbo.Audience
WHERE   VersionId = @VersionId

IF  @AudienceXml IS NOT NULL
    BEGIN
    INSERT INTO dbo.Audience (
        VersionId,
        AudienceId,
        CreatedDate,
        CreatedByPersonId,
        )
    SELECT  @VersionId,
            AudienceId,
            GETUTCDATE(),
            @PersonId
                FROM    dbo.Audience
    JOIN    @AudienceXml.nodes('/Audiences/Audience') node(c)
    ON      Audience.AudienceName = c.value('@Name', 'nvarchar(50)')
    END

将此 TSQL 包装在事务中似乎可以消除并发问题或通过更改计时来掩盖问题。但是，我认为封装在事务中并没有真正解决并发问题。

也许我的做法是错误的。感谢您的建议。

Answer 1

好吧，比尔击败了我们所有人，但这里有一个示例:

Merge dbo.Audience As target
Using   (
        Select @VersionId As VersionId, AudienceId, GetUtcDate() As CreatedDate, @PersonId As CreatedByPersonId
        From dbo.Audience
            Join @AudienceXml.nodes('/Audiences/Audience') node(c)
                On Audience.AudienceName = c.value('@Name', 'nvarchar(50)')
        )
When Matched Then
    Update 
    Set VersoinId = target.VersionId, Audience = target.AudienceId
        , CreatedDate = target.CreatedDate
        , CreatedByPersionId = target.CreatedByPersonId
When Not Matched Then
    Insert dbo.Audience(VersionId, AudienceId, CreatedDate, CreatedByPersonId)