stored procedure update

Question

There is a stored procedure that updates some columns. I want the following:
if the column value already exists it must not run update.

How can this be done?

Answer 1

You mean some rows have NULL in a column and you want to update to some value?

UPDATE yourTable
SET yourColumn = 1
WHERE yourColumn IS NULL

Answer 2

This way?

 UPDATE yourTable
 SET yourColumn = 1
 WHERE yourColumn <> 1

Otherwise please post table design as DDL, some sample data as DML statement and the expected result.

Answer 3

Hi @Chaitanya Kiran ,

We recommend that you post CREATE TABLE statements for your table together with INSERT statements with sample data and the complete code of your procedure.

If you would like to update only one column, you could refer to below:

CREATE PROCEDURE sp_updatetbl1   
(@id int,  
@col1 varchar(10)  
)  
AS  
BEGIN  
UPDATE tbl  
SET col1 = @col1  
WHERE id = @id  and col1 <> @col1   
END  

If you would like to update more than one column, you could have a try to update this table multiple times like below:

CREATE PROCEDURE sp_updatetbl2   
(@id int,  
@col1 varchar(10),  
@col2 int,  
@col3 date)  
AS  
BEGIN  
UPDATE tbl  
SET col1 = @col1  
WHERE id = @id  and col1 <> @col1   
  
UPDATE tbl  
SET col12 = @col2  
WHERE id = @id  and col2 <> @col2   
  
UPDATE tbl  
SET col3 = @col3  
WHERE id = @id  and col3 <> @col3   
END  

If you have to update multiple columns at the same time in one update statement and should not update if one or more columns have same value(s), it is impossible.

We often perform it like below:

CREATE PROCEDURE sp_updatetbl3  
(@id int,  
@col1 varchar(10),  
@col2 int,  
@col3 date)  
AS  
BEGIN  
UPDATE tbl  
SET col1 = coalesce(@col1, col1),  
col2 = coalesce(@col2, col2),  
col3 = coalesce(@col3, col3)  
WHERE id = @id  
END  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

I think that you can add a unique constraint or unique index. Use the Table Design features of Management Studio to do it manually, or execute one time a statement like this:

alter table MyTable add constraint MyConstraint unique(MyColumn)

Now MyColumn cannot be updated if a similar value already exists in another row. The UPDATE statement will give an error. (You can intercept it).

If you do not like this approach, then consider a statement like this:

update MyTable 
set MyColumn = NewValue
where MyId = SomeId
and not exists( select * from MyTable where MyColumn = NewValue)

If necessary, it can be improved to support NULL values.