need help on update statement

Question

My table and data as following,

declare @t1 table (myID varchar(200), chequeNo varchar(20));
insert into @t1 values('2011-01-03809','1925');
insert into @t1 values('2011-01-03810','1989');
insert into @t1 values('2011-01-03791','BIMB 256247');
insert into @t1 values('2011-01-03789','BIMB 256247');
insert into @t1 values('2011-01-03792','BIMB 256247');
insert into @t1 values('2011-01-03793','BIMB 256247');
insert into @t1 values('2011-13-00430','mbb 385030');
insert into @t1 values('2011-13-00431','mbb 385030');
insert into @t1 values('2011-01-03645','bmmb 003095');
insert into @t1 values('2011-08-00608','CIMB 376443');
insert into @t1 values('2011-08-00609','CIMB 385371');
insert into @t1 values('2011-04-00652','CIMB 512393');
insert into @t1 values('2011-13-00399','EBB 000639');
/* myID is a unique */

I would like to distinguish the chequeNo using update statment.

My expected result as following,

myID          | chequeNo
-----------------------------------
2011-01-03645   bmmb 003095
2011-01-03789   BIMB 256247 (1)
2011-01-03791   BIMB 256247 (2)
2011-01-03792   BIMB 256247 (3)
2011-01-03793   BIMB 256247 (4)
2011-01-03809   1925
2011-01-03810   1989
2011-04-00652   CIMB 512393
2011-08-00608   CIMB 376443
2011-08-00609   CIMB 385371
2011-13-00399   EBB 000639
2011-13-00430   mbb 385030 (1)
2011-13-00431   mbb 385030 (2)

Based on @t1 table, some chequeNo are the same although different myID. Relationship on myID to chequeNo looks like 1 to many. I want to make 1 to 1 (1 myID have a 1 chequeNo). So, I need to using update statement.

How my update statement looks like?

Possible duplicate of http://stackoverflow.com/questions/18932/sql-how-can-i-remove-duplicate-rows — Pavel Strakhov, Apr 08 '12 at 07:57
@Riateche: Related, but not really a duplicate, I think. There *have* been others, more similar questions, though. — Andriy M, Apr 08 '12 at 08:17

score 2 · Accepted Answer · answered Apr 08 '12 at 08:37

Rows can be renumbered using a ranking function.

UPDATE subquery
SET chequeNo = chequeNo + ' (' + CAST(sequence AS varchar(20)) + ')'
FROM
(
    SELECT *
    , sequence = ROW_NUMBER() OVER (PARTITION BY chequeNo ORDER BY myID)
    , reverseSequence = ROW_NUMBER() OVER (PARTITION BY chequeNo ORDER BY myID DESC)
    FROM @t1
) subquery
WHERE NOT (sequence = 1 AND reverseSequence = 1)

score 0 · Answer 2 · edited Apr 08 '12 at 09:01

0

select myid, chequeno +
' (' +
cast(ROW_NUMBER() OVER (partition by chequeno order by chequeno) as varchar) +' )' as chequeno
from @t1

edited Apr 08 '12 at 09:01

marc_s

732,580
175
1,330
1,459

answered Apr 08 '12 at 08:49

ADIL

1

Welcome to StackOverflow: if you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! – marc_s Apr 08 '12 at 09:00

need help on update statement

2 Answers2