37

How to I can update only one record in a table?

Table:

name       name1       name2
----------------------------
xx         xy          xz
xx         xx          xx
xx         xx          xx
xx         xx          xx
xy         xx          zz

Update query:

UPDATE table1 
SET name2 = '01' 
WHERE name1='xx'

I need update only one row per time.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Klapsius
  • 3,273
  • 6
  • 33
  • 56

6 Answers6

77

you can use ROWCOUNT 

SET ROWCOUNT 1

UPDATE table1 
SET name2 = '01' 
WHERE name1='xx'

SET ROWCOUNT 0

or you can use update top

UPDATE TOP (1) table1 
SET name2 = '01' 
WHERE name1='xx'
s_f
  • 804
  • 6
  • 4
  • 3
    Could you add a note about why `TOP (1)` is not written as `TOP 1`? :) – Zero3 Jun 02 '16 at 13:18
  • 1
    Parentheses are optional only in SELECT statements. Reason for that is backward compatibility. – s_f Jun 05 '16 at 20:07
  • @s_f Not sure if that's true as of SSMS 2016, won't let me do it – sparkyShorts Sep 14 '17 at 21:53
  • 11
    This should no longer considered to be the answer. "Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. Do not use SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. Also, for DELETE, INSERT, and UPDATE statements that currently use SET ROWCOUNT, we recommend that you rewrite them to use the TOP syntax." – pmcilreavy Feb 07 '18 at 21:47
  • Thanks. Good to know about that change for ROWCOUNT. Also in that answer you can find two solutions including TOP syntax that you mentioned in your quote. – s_f Feb 08 '18 at 23:17
2
UPDATE table1 
SET name2 = '01' 
WHERE name1='xx'
LIMIT 1;
Jerry
  • 31
  • 1
  • 1
    Welcome to Stack Overflow. Code-only answers are strongly discouraged. Please [include an explanation](https://meta.stackoverflow.com/q/392712/13138364) of how and why this solves the problem. This will help future readers to better understand your solution. – tdy Oct 28 '21 at 04:21
  • SQL Server doesn't have a `limit` clause. – Mark Rotteveel Oct 28 '21 at 09:43
  • LIMIT works with Ver 8.0.15 for linux. It is a simple solution for updating one row with data when the only unique identifier is a row id. – Stephen Apr 27 '23 at 11:51
0

if you are using Oracle then use the following update TABLE_NAME set COLUMN_NAME=VALUE where name1='xx' and rownum=1

Mhd Wael Jazmati
  • 636
  • 9
  • 18
-2

Please use subquery operating on primary key for better performance

-- INVALID, BUT EXPECTED: update "user" set email = 'login@com.com' where email = 'login2@com2.com' limit 1

update "user' set email = 'login2@com2.pl' where id = (select id from "user" where email = 'login@com.com' limit 1)
test30
  • 3,496
  • 34
  • 26
-4

You can just add LIMIT 1 at the end of the query.

Thomas
  • 9
  • Sql server does not support LIMIT – Luca Jan 12 '17 at 17:24
  • It does support TOP though, top can be used in a similar fashion as limit. https://learn.microsoft.com/en-us/sql/t-sql/queries/top-transact-sql?view=sql-server-ver15 – Oskar Mar 07 '22 at 12:40
-6

if you want update one row per time, please try to add an Identity Column to your table to identify each row.

Dotnetter
  • 261
  • 1
  • 5