在表上执行UPDATE时出现sql错误

本文关键字：sql 错误 UPDATE 执行 | 更新日期: 2023-09-27 18:04:56

错误信息

消息2601,14级，状态1，行1dbo对象"。RTU_ADDRESS'具有唯一索引'唯一键RTU'。的duplicate key value为(2,5,4,6,500,610,y)。的语句已被终止。

Table

脚本
USE [hts]
GO
/****** Object:  Table [dbo].[RTU_ADDRESS]    Script Date: 03/19/2013 21:07:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[RTU_ADDRESS](
    [RowNo] [int] IDENTITY(1,1) NOT NULL,
    [StationId] [int] NULL,
    [SystemId] [int] NULL,
    [CCNumber] [int] NULL,
    [LineNumber] [int] NULL,
    [RTUNumber] [int] NULL,
    [SRTUNumber] [int] NULL,
    [Description] [char](100) NOT NULL,
    [SDescription] [char](100) NOT NULL,
    [Area_ID] [char](10) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

索引脚本

USE [hts]
GO
/****** Object:  Index [Unique Key RTU]    Script Date: 03/19/2013 21:08:31 ******/
CREATE UNIQUE NONCLUSTERED INDEX [Unique Key RTU] ON [dbo].[RTU_ADDRESS] 
(
    [StationId] ASC,
    [SystemId] ASC,
    [CCNumber] ASC,
    [LineNumber] ASC,
    [RTUNumber] ASC,
    [SRTUNumber] ASC,
    [Area_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
c#代码:

SqlDataAdapter sqlAdapter = null;
                strSelectCmd = "SELECT RowNo FROM RTU_ADDRESS";
                SqlCommand sqlCmd = new SqlCommand();
                sqlCmd.Connection = m_SqlConn;
                sqlCmd.CommandText = strSelectCmd;
                int i = Convert.ToInt32(sqlCmd.ExecuteScalar());
                sqlAdapter = new SqlDataAdapter();
                sqlAdapter.SelectCommand = sqlCmd;

                strInsCmd = "UPDATE RTU_ADDRESS SET RTUNumber='" + rtunum.Text + "', SRTUNumber='" + srtunum.Text + "', Description='" + desc.Text + "', SDescription='" + sdesc.Text + "' WHERE Area_ID='" + area_ID.Text + "' AND StationId='" + station.Text + "' AND SystemId='" + sys.Text + "' AND CCNumber='" + ccnum.Text + "' AND LineNumber='" + linenum.Text + "'";
                strErrorMsg = Utilities.ExecuteSQLCommand(m_SqlConn, strInsCmd);
                string check = "duplicate";
                bool d = check.Any(strErrorMsg.Contains);
                if (d == true)
                {
                    MessageBox.Show(" Duplicates Entries Found", "Database Insertion");
                }
                else if (string.IsNullOrEmpty(strErrorMsg) == false)
                {
                    MessageBox.Show(this, "Error!Unable to Add New RTU Configurations!", "RTU Configurations error");
                }
                else
                {
                    d = false;
                    this.Close();
                }

我现在不知道该怎么办了，我没有放任何副本。

我只是想更新rtunnumber, srtunnumber, Description和SDescription，同时保持我的stationID,systemID,CCNumber,LineNumber和Area_ID不变。请帮助

更新:Select语句

SELECT * FROM RTU_ADDRESS WHERE Area_ID='IUY ' AND StationId='2' AND SystemId='5' AND CCNumber='4' AND LineNumber='6'

给我2个结果，但rtunnumber和srtunnumber是不同的。

请帮

在表上执行UPDATE时出现sql错误

很明显，您尝试进行的更新将在表中生成副本。您是否尝试过根据您试图使用更新的值对表进行选择?您很可能会找到一个现有的记录…

查看更新语句的where子句中的条件，如果有多个具有相同AreaId, SystemId, StationId, CCNumber和LineNumber的记录，则会得到此错误，因为您试图使用相同的rtunnumber和srtunnumber更新它们。您可以通过运行以下sql:

来检查这一点
SELECT AreaId, SystemId, StationId, CCNumber, LineNumber, COUNT(*)
FROM RTU_ADDRESS
GROUP BY AreaId, SystemId, StationId, CCNumber, LineNumber
HAVING COUNT(*) > 1

如果返回任何行，则会得到"duplicate"错误。

事实上，你的编辑版本是这样说的。虽然这两个记录可能具有不同的SRTUNumber和RTUNUmber，但您正在尝试在更新语句中为它们提供相同的SRTUNumber和RTUNUmber。

相关文章：