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

Question

I have a table in SQL Server that has a PK (ID) and another (logical) primary key made by a couple of other columns (although there is no UNIQUE constraint on that). Let's say, table PERSON, PK = PERSON_ID, then NAME, SURNAME, AGE

I'd like it was possible to say

UPDATE PERSON SET AGE = 43 WHERE NAME = 'XX' AND SURNAME = 'YYY'

and have it executed only if 'updated rows' = 1, otherwise (more than 1 row) NO EXECUTION at all. The problem is that I'm not sure if NAME and SURNAME uniquely identify a record, and I have no way to tell it a priori.

Ideas?

possible duplicate of [SQL update top1 row query](http://stackoverflow.com/questions/3860975/sql-update-top1-row-query) and [Update a single row with t-sql](http://stackoverflow.com/questions/2909118/update-a-single-row-with-t-sql) and [SQL query to update top 1 record in table](http://stackoverflow.com/questions/3855528/sql-query-to-update-top-1-record-in-table) — Kermit, Feb 01 '13 at 16:03
@njk -- not a duplicate; in this case, the user only wants to execute if one row would be updated, and not simply to update the first record. — LittleBobbyTables - Au Revoir, Feb 01 '13 at 16:04

score 8 · Answer 1 · edited Feb 01 '13 at 16:18

8

Try the below query... it will help you

UPDATE PERSON 
SET AGE = 43 
WHERE NAME = 'XX' 
  AND SURNAME = 'YYY' 
  AND 1 = (SELECT COUNT(*) FROM PERSON WHERE NAME = 'XX' AND SURNAME = 'YYY)

edited Feb 01 '13 at 16:18

marc_s

732,580
175
1,330
1,459

answered Feb 01 '13 at 16:11

Pandian

8,848
2
23
33

I've chosen this one because it was the shortest one :). Thx to all. – Diego Pascotto Feb 04 '13 at 10:13

score 6 · Answer 2 · answered May 04 '17 at 06:52

Rather than writing a complex WHERE clause or IF statement, I usually just wrap the whole thing in a transaction and check @@ROWCOUNT:

BEGIN TRAN
UPDATE PERSON SET AGE = 43 WHERE NAME = 'XX' AND SURNAME = 'YYY'
IF @@ROWCOUNT > 1 ROLLBACK TRAN ELSE COMMIT TRAN

score 5 · Answer 3 · answered Feb 01 '13 at 16:51

5

I'd put the conditional before the update rather than in the where clause.

IF (SELECT COUNT(*) FROM PERSON WHERE NAME = 'XX' AND SURNAME = 'YYY') = 1
UPDATE PERSON SET AGE = 43 WHERE NAME = 'XX' AND SURNAME = 'YYY'

answered Feb 01 '13 at 16:51

Ben

309
1
3

Perfect! Thanks! – Sirar Salih Apr 20 '22 at 13:50

score 1 · Answer 4 · answered Feb 01 '13 at 16:05

something like that should do it.

UPDATE Person p1
SET p1.AGE = 43
WHERE NAME = 'XX' AND SURNAME = 'YY'
AND NOT EXISTS (
  SELECT NULL FROM Person p2
  WHERE p1.NAME = p2.NAME
  AND p1.SURNAME = p2.SURNAME
  GROUP BY p2.NAME, p2.SURNAME
  HAVING COUNT(*) > 1)

score 1 · Answer 5 · answered Feb 01 '13 at 19:53

1

UPDATE Person p1
SET p1.AGE = 43
WHERE NAME = 'XX' AND SURNAME = 'YY'
AND NOT EXISTS (
  SELECT NULL FROM Person p2
  WHERE p1.NAME = p2.NAME
  AND p1.SURNAME = p2.SURNAME
  AND p1.ID <> p2.ID)

answered Feb 01 '13 at 19:53

paparazzo

44,497
23
105
176

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

5 Answers5

Linked

Related