Query - 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 2005 Forums

Transact-SQL (2005)

Query

Author

Topic

8022421
Starting Member

45 Posts

Posted - 2009-04-20 : 02:18:59

I have a table with the below format.

PONumber PaymentNO RemitMethod
331 1214772 ACH
346 3910005909 CHECK
348 3910005910 CHECK

I have to Check, If the first Payment Number is different than the Second payment Number then I have to Update the First Payment number to the entire Table.(In this Case the first Payment Number is 1214772, the Second Payment number is 3910005909 which is different than the first, So I have to Update 1214772 to all the three rows for the Payment Number column ) this should be done with out using cursors. Please advice.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-20 : 03:08:15

What do you want to do when new data are added to table?

Madhivanan

Failing to plan is Planning to fail

8022421
Starting Member

45 Posts

Posted - 2009-04-20 : 03:56:28

This is a Temp table which is populated on the Fly..After populating the temp table I will just check the First payment Number with the Second Payment Number if different Just update the First paymnet to all the paymnet Numbers.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-20 : 04:07:54

Which is the unique column in the temp table?

Madhivanan

Failing to plan is Planning to fail

8022421
Starting Member

45 Posts

Posted - 2009-04-20 : 05:12:44

PONumber.

aprichard
Yak Posting Veteran

62 Posts

Posted - 2009-04-20 : 07:16:38

create table #Test(PONumber int, PaymentNO varchar(15), RemitMethod varchar(10))
insert into #Test values(331,'1214772','ACH')
insert into #Test values(346,'3910005909','CHECK')
insert into #Test values(348,'3910005910','CHECK')

select * from #Test

declare @PayNo Varchar(200), @Index int
set @PayNo=''

select top 2 @PayNo=@PayNo + Paymentno + ',' from #Test
set @PayNo=left(@PayNo,len(@PayNo)-1)
select @PayNo
--set @Index=charindex(@PayNo,',')
if substring(@PayNo,1,@Index-1) <> substring(@PayNo,@Index+1, len(@PayNo))
put update statement
drop table #Test

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-20 : 08:20:34

This query will update all other rows (except the first row) in the temp table with the PaymentNo from the first row. There is no need to "check" whether the first row and the second row PaymentNo matches because you're going to update it ANYWAYS if they don't. So might as well update it.

UPDATE TempTable SET PaymentNo = (SELECT TOP 1 PaymentNo FROM TempTable ORDER BY PONUmber) WHERE PoNumber NOT IN (SELECT TOP 1 PaymentNo FROM TempTable ORDER BY PONUmber)

I don't know what the remitcolumn is used for??? If "check" in the remitcolumn means to check the row with the "ach" row, then this query can be optimized.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources