如果 vb.net 中不存在，请进行更新

Question

谁能帮我解决我的查询。如果syscode不存在，我想更新我的数据，但是当我尝试更新sysdesc时，它失败了，因为syscode已经存在。

    Dim query As String
    Dim rowsAffected As Integer
    query = "UPDATE tbl_sysname SET syscode = @code, sysdesc = @desc " & _
        "WHERE id = @id AND NOT EXISTS  (SELECT syscode FROM tbl_sysname WHERE " & _
        "syscode = @code) "
    cmd = New SqlCommand(query, cn)
    cmd.Parameters.Add("@code", SqlDbType.VarChar).Value = txtCode.Text
    cmd.Parameters.Add("@desc", SqlDbType.VarChar).Value = txtDesc.Text
    cmd.Parameters.Add("@id", SqlDbType.VarChar).Value = lblId.Text
    rowsAffected = cmd.ExecuteNonQuery
    'MsgBox(rowsAffected)
    If rowsAffected >= 1 Then
        'reader = cmd.ExecuteReader
        MsgBox("Update Successfully")
        LVSysName.Items.Clear()
        'reader.Close()
        GetLvData() 'This will display the data of LvSysName
    Else
        MsgBox("Failed to Update")
    End If

我想在可以更新sysdesc的同时避免syscode重复。帮助我解决我的查询或给我一个关于我应该怎么做的建议

Answer 1

为此

，您需要使用两个UPDATE语句。没有办法一蹴而就。

UPDATE tbl_sysname 
SET syscode = @code
WHERE id = @id AND 
  NOT EXISTS  (SELECT syscode FROM tbl_sysname WHERE syscode = @code)
UPDATE tbl_sysname 
SET sysdesc = @desc
WHERE id = @id