Skip the update query if it affects more than one row

Question

Possible Duplicate:
How to execute an UPDATE only if one row would be affected?

I have an update query in SQL Server 2005

update custom_graphics_files 
set actual_file_Name = @CorrectName 
where actual_file_Name = @FileName

Now if there are more than one actual_file_name, I want to skip the update query,

score 1 · Answer 1 · answered Feb 04 '13 at 14:11

update t
set t.actual_file_Name = @CorrectName 
FROM custom_graphics_files t
INNER JOIN
(
   SELECT actual_file_Name, COUNT(*) TheCount
   FROM custom_graphics_files 
   GROUP BY actual_file_Name
) t2 ON t.actual_file_Name = t2.actual_file_Name AND TheCount = 1
where t.actual_file_Name = @FileName;

score 0 · Answer 2 · answered Feb 04 '13 at 14:20

I like using window functions for this purpose:

with toupdate as (
      select cgf.*, COUNT(*) over (PARTITION by actual_file_name) as ActCnt
      from custom_graphics_files
     )
update toupdate
    set actual_file_Name = @CorrectName 
    where actual_file_Name = @FileName and ActCnt = 1

On a large table, this may not be the most efficient solution, depending on the selectivity of actual_file_Name = @FileName.

score 0 · Answer 3 · answered Feb 04 '13 at 14:27

0

This is the most "readable" query you can get :

update custom_graphics_files 
set actual_file_Name = @CorrectName 
where actual_file_Name = @FileName
and (select count(1) from custom_graphics_files where actual_file_Name = @FileName) = 1

answered Feb 04 '13 at 14:27

Dominic Goulet

7,983
7
28
56

Skip the update query if it affects more than one row

3 Answers3