仅当一行受到影响时如何执行更新? [英] How to execute an UPDATE only if one row would be affected?

问题描述

我在 SQL Server 中有一个表，它有一个 PK (ID) 和另一个(逻辑)主键，由其他几个列组成(尽管没有 UNIQUE 约束).比方说，表 PERSON, PK = PERSON_ID, 然后 NAME, SURNAME, AGE>

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

我想说

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

并且仅在 'updated rows' = 1 时才执行它，否则(超过 1 行)根本没有执行.问题是我不确定 NAME 和 SURNAME 是否唯一标识一条记录，而且我无法先验地告诉它.

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.

想法?

推荐答案

试试下面的查询...它会帮助你

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)

这篇关于仅当一行受到影响时如何执行更新?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！