CODEWITHSUNDEEP
sqlsql-serverdatabase
Fylix
Fylix

Reputation: 2723

update rows and ignore the one that causes primary key constraint?

I have a table with the following constraint definition.

CONSTRAINT [PK_TempItemTable_1] PRIMARY KEY CLUSTERED 
(
   [A] ASC,
   [B] ASC,
   [D] ASC,
   [D] ASC,
   [E] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

I'm attempting to update the column D with values (about 9000 plus rows); Due to update values, the constraint error arrives because some value I want to update into D.

Violation of PRIMARY KEY constraint 'PK_TempItemTable_1'. Cannot insert duplicate key in object 'dbo.TempItemTable'.

Is there a way for me to write the MS SQL statement that let me update all other rows and ignore the one that will cause this primary key constraint?

Thanks

Upvotes: 0

Views: 1891

Answers (1)

TheGameiswar
TheGameiswar

Reputation: 28940

Two options..

1.Create constraint with IGNORE_DUP_KEY = ON and later delete duplicates and set constraint back.

2.Primary key is a combination of columns from A to E,check ,so not sure how checking only d will help,but with out knowing sample schemas,i can say ,this will exclude all values which are present in table you are going to update

update mt
set d=dup.d
from
maintable t
join
ninetythousandtable dup
on dup.d<>t.d

Upvotes: 1

Related Questions