How to execute an UPDATE only if one row would be

问题:

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?

回答1:

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)

回答2:

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'

回答3:

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

回答4:

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)

回答5:

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)