Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lappin
Posting Yak Master
182 Posts |
 Posted - 2011-03-11 : 11:14:31
|
| I have a table with Primary Key of two fields: [ID] and [Status].I have a webpage which sends an XML parameter to a Stored Procedure.I am using MERGE to update the table using the XML string. My Question is how do I handle an UPDATE which would result in a duplicate primary key. For example, if there are two rows ID='123' with [Status]='A' and [Status]='D'If the user tries to change the status from 'A' to 'D' it will fail because of PK.Here is section of code, I use @OldStatus as value before change and @Status as changed value.;WITH TestCTE AS ( SELECT x.h.value('@ID', 'VARCHAR(11)') AS [ID], x.h.value('@OldStatus', 'char(1)') AS OldStatus, x.h.value('@Status', 'char(1)') AS [Status], x.h.value('@FieldA', 'char(11)') AS [FieldA] FROM @XMLTest.nodes('/root/row') AS x(h) ) MERGE dbo.TestTable AS Target USING TestCTE AS Source ON (Target.[ID]= Source.[ID]and Target.[Status]= Source.OldStatus) WHEN MATCHED THEN UPDATE SET Target.FieldA= Source.FieldA,-- the following line will fail if [Status] already exists Target.[Status] = Source.[Status] WHEN NOT MATCHED THEN INSERT (.........etc.... ;Is there a way to check if the "new value" already exists and handle this, either by modifying /deleting one of them or returning something to user. |
|
|
|
|
|
Merge update PK already exists