need help on update statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

need help on update statement

Author

Topic

Idyana
Yak Posting Veteran

96 Posts

Posted - 2012-04-07 : 06:21:46

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)

How my update statement looks like?

mfemenel
Professor Frink

1421 Posts

Posted - 2012-04-07 : 06:59:08

I'm sorry I read this several times and I'm not sure I understand what you mean by "distinguish the chequeNo using update statement"

Mike
"oh, that monkey is going to pay"

Idyana
Yak Posting Veteran

96 Posts

Posted - 2012-04-07 : 10:44:50

Hi Sir,

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.

The problem is, I have no idea how's my update statement looks like

Idyana
Yak Posting Veteran

96 Posts

Posted - 2012-04-07 : 22:01:38

Really need help

adilbsc2004
Starting Member

1 Post

Posted - 2012-04-08 : 06:03:04

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');

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

from @t1

quote:

Originally posted by Idyana

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)

How my update statement looks like?

Mohammed Adil

Sachin.Nand

2937 Posts

Posted - 2012-04-08 : 06:31:43

Why you don't want to assign value of "1" to cheque numbers which are unique ?

After Monday and Tuesday even the calendar says W T F ....

Idyana
Yak Posting Veteran

96 Posts

Posted - 2012-04-08 : 11:24:38

Mr. adilbsc2004 give me the hint how to built the update statement

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-08 : 18:10:13

why cant you maintain a different column for it? why append sequence to description?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-09 : 06:52:59

This may be of some help. You could change the data and take it to a new temp table like this:


Declare @temp Table(myId varchar(30), ChequeNo varchar(30) )
;With CTE
As
(Select myID, ChequeNo+' '+'('+Cast(ROW_NUMBER()
Over(Partition By ChequeNo Order By myId) As varchar(30) )+')' As ChequeNo From @t)
Insert into @temp
Select * From CTE

If you want to do all this in the same table, just delete the data and insert fresh from the temp table. That adds this code to the above code :


Truncate Table @t
Insert Into @t
Select * From @temp

But, there is no logic in truncating and inserting data from the new table. Just use the new table instead.

Hope this was helpful.

Vinu Vijayan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources