-1

I have a SQL Server table with three columns, the first two columns are the primary key. I'm writing a stored procedure that will update the last two columns in mass and it works fine for that as long as there are are no primary key violations but when there is a primary key violation it throws an error and stops executing.

How can I make it to ignore the line and continue updating the record as long as there is no primary key violation?

Is there a better way to approach this problem? I'm only doing a simple update with where as column2= somevalue AND column 3 = some value.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
Tex
  • 11
  • 1
  • 5

2 Answers2

1

In SQL Server you'd use MERGE to upsert (i.e. insert or update):

MERGE mytable
USING (SELECT 1 as key1, 2 as key2, 3 as col1, 4 as col2) AS src
  ON (mytable.key1 = src.key1 AND mytable.key2 = src.key2)
WHEN MATCHED THEN   
  UPDATE SET col1 = src.col1, col2 = src.col2
WHEN NOT MATCHED THEN  
  INSERT (key1, key2, col1, col2) VALUES (src.key1, src.key2, src.col1, src.col2);
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • Thank you. I'll try that tomorrow and let you know. I'm not exactly sure why you're using insert as I'm not trying to insert new record but only update if it already exists. – Tex May 30 '17 at 23:12
  • Sorry, you are right. I misread your question. You are only updating, but you are updating the *primary key*. So please ignore this answer. I am going to delete it later. As to what you are doing: *Never* update a primary key. It is meant to identify a row for its lifetime. If you think you must update it, this is very likely a sign for a bad database design. – Thorsten Kettner May 31 '17 at 05:33
0

There is nothing inherently wrong with your question, despite the rather loud protestations. Your question is confusing, especially when you refer to columns by position. That is a big no-no. So, a script that demonstrates your problem is generally the best way to both demonstrate your problem and get useful suggestions.

The short answer to your question is - you can't. A statement either succeeds or fails as a whole. If you want to update each row individually and ignore certain errors, then you need to write your tsql to do that.

And despite the protests (again), there are situations where it is necessary to update columns that are part of the primary key. It is unusual - very unusual - but you should also be wary of any absolute statement about tsql. When you find yourself doing unusual things, you should review your schema (and your approach) because it is quite possible that there are better ways to accomplish your goal.

And in this case, I suggest that you SHOULD really think about what you are trying to accomplish. If you want to update a set of rows in a particular way and the statement fails - that means there is a flaw somewhere!. Typically, this error implies that your update logic is not correct. Perhaps you assume something about your data that is not accurate? It is impossible to know from a distance. The error message will tell you what set of values caused the conflict - so that should give you sufficient information to investigate. As another tool, write a select statement that demonstrates your proposed update and look for the values in the error message. E.g. 

set nocount on;
create table #x (a smallint not null, b smallint not null, c varchar(10) not null, constraint xx primary key(a, b)); 

insert #x (a, b, c) values (1, 1, 'test'), (1, 2, 'zork'); 
select * from #x;

update #x set b = 2, c = 'dork';

select a, b, c, cast(2 as smallint) as new_b, 'dork' as new_c 
from #x 
order by a, new_b;

drop table #x;
SMor
  • 2,830
  • 4
  • 11
  • 14