update only 1 of the 2 identical rows

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi All,

I have a scenario where 2 rows in a table are exactly identical and I have to update only 1 of them.
Is it possible ???

Below is a sample case :

SQL

CREATE TABLE #AAA
(col1 INT , col2 VARCHAR(100))

INSERT INTO #AAA
VALUES (1, 'XXX')

INSERT INTO #AAA
VALUES (1, 'XXX')

INSERT INTO #AAA
VALUES (2, 'YYY')

INSERT INTO #AAA
VALUES (3, 'ZZZ')

SELECT * FROM #AAA

I want to update only 1 row where col1 is 1.

Regards,
Gopal

Posted 3-Nov-11 21:03pm

gopalgupta

Add a Solution

3 solutions

Solution 1

In the absence of a unique key for your rows then it is not possible to single out a specific row for updating.

EDIT : I stand corrected, you can use ROWCOUNT

Posted 3-Nov-11 21:06pm

Mehdi Gholam

Updated 3-Nov-11 21:15pm

v2

Comments

Prerak Patel 4-Nov-11 3:08am

It can be done using SET ROWCOUNT, see my answer.

Solution 3

Basically SQL UPDATE statement is worked on WHERE condition. if you not give any where condition, it will update all rows.

SQL

UPDATE table SET field=value WHERE status=AVAILABLE LIMIT 1

OR

SQL

SET ROWCOUNT 1
Update workq set status = 1

OR

SQL

Update workq set status = 1
Where ID = (Select Top 1 ID from workq)

Posted 3-Nov-11 21:13pm

koolprasad2003

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Prerak Patel · Accepted Answer · 2011-11-03T21:07:00

Solution 2

SQL

SET ROWCOUNT 1
update #AAA set col1=4, col2='XXX.NEW' where col1=1

http://msdn.microsoft.com/en-us/library/ms188774.aspx[^]

Posted 3-Nov-11 21:07pm

Prerak Patel

v2

Comments

Mehdi Gholam 4-Nov-11 3:10am

I stand corrected and learned something, 5+ for that,

gopalgupta 4-Nov-11 3:12am

gr8 .....
thnx buddy

RaviRanjanKr 4-Nov-11 3:18am

My 5+

update only 1 of the 2 identical rows

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

update only 1 of the 2 identical rows

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us