optimal update against large table

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

General SQL Server Forums

New to SQL Server Programming

optimal update against large table

Author

Topic

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2009-10-12 : 05:31:41

hi,

this is a small case:


create table test
(customerID int
,signature char(5)
,invoiceID int
,entryDate smalldatetime
,expiryDate smalldatetime
,valid char(1)
)

insert into test
select 123, 'SIG_A', 3255, '20091001', null, null union all
select 123, 'SIG_B', 4264, '20091009', null, null union all
select 123, 'SIG_A', 4351, '20091009', null, null union all
select 123, 'SIG_A', 4353, '20091011', null, null union all
select 231, 'SIG_A', 2425, '20081224', null, null union all
select 231, 'SIG_A', 4012, '20090823', null, null

what would be the optimal update for field "expiryDate" and field "valid (values (Y|N))" in case when customer "CustomerID"
have two signatures 'SIG_A'. Field expiryDate must be updated with previous last EntryDate, all previous fields Valid must be update with value N, current valid signature 'SIG_A' must have valid null and expirydate also null.

p.s.: i'm using sql 2000.

thank you for ideas.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2009-10-12 : 06:27:23

this is the sample code; but i'm still looking for faster|better|optimied solution.

thank you


select 
t1.customerID as customerID
,max(t1.entryDate) as cas_vnosa
,t1.invoiceID 
,(select 
	 min(t2.entryDate)
		from Test as t2
			where
				t2.customerid = t1.customerid
			and t2.signature = 'SIG_A'
			and t2.invoiceID > t1.invoiceID
 	) as previous_cas_vnosa
from test as t1
where t1.signature = 'SIG_A'
group by t1.customerID, t1.invoiceID
order by t1.customerID, max(t1.entryDate)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources