CODEWITHSUNDEEP
sqlsql-serversql-update
Klapsius
Klapsius

Reputation: 3359

How to update only one row in a table?

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.

Upvotes: 39

Views: 112426

Answers (6)

test30
test30

Reputation: 3654

Please use subquery operating on a primary key for better performance

-- INVALID, BUT EXPECTED: update "user" set email = '[email protected]' where email = '[email protected]' limit 1

update "user' set email = '[email protected]' where id = (
    select id from "user" where email = '[email protected]' limit 1
)

Upvotes: -2

Mhd Wael Jazmati
Mhd Wael Jazmati

Reputation: 667

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

Upvotes: 0

Jerry
Jerry

Reputation: 31

UPDATE table1 
SET name2 = '01' 
WHERE name1='xx'
LIMIT 1;

Upvotes: 2

Thomas
Thomas

Reputation: 9

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

Upvotes: -4

s_f
s_f

Reputation: 874

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'

Upvotes: 84

Dotnetter
Dotnetter

Reputation: 261

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

Upvotes: -6

Related Questions