CODEWITHSUNDEEP
sql-serversql-update
user2168350
user2168350

Reputation: 11

SQL server update error - Msg 2627

I am trying to update my table "tender".

It has these columns:

I tried the below code:

update tender 
set te_status='D'
where rq_no like 'Q052401C' AND te_year like '2012' and te_no like 'n0066'

if I place any other character in place of 'D' in te_status, update is successful except for 'D' e.g.:

set te_status='S'

When I run

SELECT * FROM TENDER
WHERE
    rq_no LIKE 'Q052401C' AND te_year LIKE '2012' AND te_no LIKE 'n0066'

, it returns a single row.

Ιt shows me the following error:

Msg 2627, Level 14, State 1, Line 1 Violation of PRIMARY KEY constraint 'PK_tender'. Cannot insert duplicate key in object 'dbo.tender'. The duplicate value is (N0066, Q052401C, D, 2012) The statement has been terminated.

What does all these mean?

Upvotes: 1

Views: 13266

Answers (1)

Roger Rowland
Roger Rowland

Reputation: 26279

It means you have a primary key defined on that table, which consists of the fields relating to the values it displayed - i.e. (N0066, Q052401C, D, 2012) - and that if you change the te-status field to D on the row you are updating, then this results in a primary key that already exists.

Depending on the DB you use, you sometimes can't UPDATE a primary key field - you should do a DELETE of the old row and an INSERT of the changed row (with the new key).

Upvotes: 2

Related Questions