Update one row only out of two identical rows in sybase

Question

I have three rows in the database out of those two are identical. Out of those two same rows I have to make changes in one using the sybase.Ex.

Row1: ABC 456 ancient block
Row2: ABC 456 ancient block
Row3: DEF 678 class   block

I have to make changes in one of the first two block by changing ABC to XYZ.If there are only two identical blocks then I am doing the below method.

begin transaction AA
set rowcount 1
update table 
set col1 = XYZ
where col1 = ABC
commit transaction AA
set rowcount 0

It is easy if there are two identical rows but if two identical and one different then sybase picks the unique row and updates it. Can someone tell how to solve this three rows problem ? I am using aseisql for the Sybase.

Your question is unclear. If the 3rd record is not identical to the first two, why would it be in the result set. Maybe add a 4th row to your example set that shows a record that is identical, but unique? — Mike Gardner, Apr 14 '14 at 14:41
Actually I dont need to manipulate the row that is not identical to the first two. I have to update only one of the rows that are identical. — 10hero, Apr 16 '14 at 10:57
"if two identical and one different then sybase picks the unique row and updates it" <-- this is my confusion, you are talking about identical and unique rows in the same query set. — Mike Gardner, Apr 16 '14 at 13:27
Yes. Thats the problem that sybase picks the unique rows only to update. But I have found a crude solution but it works. — 10hero, Apr 17 '14 at 19:08

score 1 · Accepted Answer · answered Apr 16 '14 at 11:26

1

Have you tried:

update top 1 table 
set col1 = XYZ
where col1 = ABC

answered Apr 16 '14 at 11:26

Michał Szkudlarek

1,443
1
21
35

Hi, the solution you have provided will update both the rows . And this is not required . – 10hero Apr 17 '14 at 19:06
Actually I had same problem as you and manage to fix it with mentioned query. Only one record was updated. – Michał Szkudlarek Apr 18 '14 at 10:55

score 0 · Answer 2 · answered Apr 17 '14 at 19:22

This is the solution I figured.

begin transaction a
set rowcount 2
update table 
set col1 = XYZ
where col4 = block

commit transaction a
set rowcount 0

It will update one of the duplicate rows and the unique row. Then I will update the unique row to its original value separately using the update statement. I know its sound crude but no one has answered satisfactorily.

Update one row only out of two identical rows in sybase

2 Answers2