Complicated Select and Update statements

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)

Complicated Select and Update statements

Author

Topic

Torch
Starting Member

20 Posts

Posted - 2010-06-21 : 22:20:06

Hi,

I have an EVENTS table with the following column names: eventid, attributename, userid, attributevalue, sourcesystem, status, timeoccurred, timeprocessed.

An example row of data for this table would would be: 5, Email, PETER.SMITH6, peter.burton6@det.nsw.edu.au, REC, NULL, 16/04/2009, 15:30, 5/05/2009, 14:31

This table has thousands of rows of data.

We want to select all rows where status is null and we only want to pick up 500 rows at a time (every time we poll the database table).

However, here is the tricky bit. The table can have multiple entries for the same event id. We want to pick up eventid's together.

Consider the following. Say we had 10 rows of data. The first three rows had eventid 1, the next three rows eventid 2, the next four rows have eventid 4. Now imagine we want to pick up 4 rows at a time. The first time round we would pick up the three rows of eventid 1. Even though we could pick up one more row we dont since we cant pick up all the eventid 2's or eventid 3's. The three rows of eventid 2 would be picked up in the next round. However, if there had only been one row of eventid 2, then the three rows of eventid 1 and the one row of eventid 2 could be picked up at once!

Clear as mud?!?

Need your help on this one!

Also, just to make it worse, we also want to Update status to 'S' and timeprocessed to 'systimestamp' after the select statement.

Thanks,
Torch

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-21 : 22:23:14

have to agree with you that it is clear as mud

Care to post some table structure with some sample data and the expected result ?

KH
[spoiler]Time is always against us[/spoiler]

Torch
Starting Member

20 Posts

Posted - 2010-06-22 : 00:13:09

Ok. Take this sample set of data from my table:

EVENTID ATTNAME USERID ATTVALUE SOURCESYSTEM STATUS TIMEOCCURRED TIMEPROCESSED
5 Email P.B6 p.b6@abc.com REC NULL 16/04/2009 15:30 5/05/2009 14:31
5 Surname P.B6 Burton REC NULL 16/04/2009 15:30 5/05/2009 14:31
5 GivenName P.B6 Peter REC NULL 16/04/2009 15:30 5/05/2009 14:31
5 EmployeeId P.B6 100000018 REC NULL 16/04/2009 15:30 5/05/2009 14:31
7 Email T.R t.r@abc.om REC NULL 16/04/2009 15:30 5/05/2009 14:31
7 Surname T.R REILLY REC NULL 16/04/2009 15:30 5/05/2009 14:31
7 GivenName T.R TERENCE REC NULL 16/04/2009 15:30 5/05/2009 14:31
7 EmployeeId T.R 6811589 REC NULL 16/04/2009 15:30 5/05/2009 14:31
9 Email T.H T.H@abc.com REC NULL 16/04/2009 15:34 5/05/2009 14:31
9 Surname T.H Hegglin REC NULL 16/04/2009 15:34 5/05/2009 14:31
9 GivenName T.H Thomas REC NULL 16/04/2009 15:34 5/05/2009 14:31

It has 11 rows of data in total. Say I want to select a maximum 5 rows at a time (every time my application polls this database table). The select should get the rows which have a status of NULL but also pick up groups of eventID.

So for example on the first select it would retrieve:
5 Email P.B6 p.b6@abc.com REC NULL 16/04/2009 15:30 5/05/2009 14:31
5 Surname P.B6 Burton REC NULL 16/04/2009 15:30 5/05/2009 14:31
5 GivenName P.B6 Peter REC NULL 16/04/2009 15:30 5/05/2009 14:31
5 EmployeeId P.B6 100000018 REC NULL 16/04/2009 15:30 5/05/2009 14:31

Even though it has only picked up four rows thats ok. Because as you can see there are four rows of eventid 7 and three rows of eventid 9.
If however there had been only one row of eventid 7, then sending four rows of eventid 5 and one row of eventid 7 would be correct.

Hope this makes it a bit more clear.

Thanks,
Torch

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-22 : 00:15:51

what if there are 6 rows of an eventid ? More than you max of 5 ?

KH
[spoiler]Time is always against us[/spoiler]

Torch
Starting Member

20 Posts

Posted - 2010-06-22 : 00:20:32

I simply put this up as an example. Ignore the case where there may be more than 5 eventids.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-22 : 00:31:22

[code]
declare @events table
(
eventid int,
picked int default 0
)

insert into @events(eventid)
select 5 union all
select 5 union all
select 5 union all
select 5 union all
select 7 union all
select 7 union all
select 7 union all
select 7 union all
select 9 union all
select 9 union all
select 9 union all
select 10

declare @max int

select @max = 5

-- First Pick
; with events
as
(
select eventid, picked
from @events
where picked = 0
)
update e
set picked = 1
output inserted.eventid
from events e
cross apply
(
select cnt = count(*)
from events x
where x.eventid <= e.eventid
) c
where c.cnt <= @max

-- Second Pick
; with events
as
(
select eventid, picked
from @events
where picked = 0
)
update e
set picked = 1
output inserted.eventid
from events e
cross apply
(
select cnt = count(*)
from events x
where x.eventid <= e.eventid
) c
where c.cnt <= @max

-- Third Pick
; with events
as
(
select eventid, picked
from @events
where picked = 0
)
update e
set picked = 1
output inserted.eventid
from events e
cross apply
(
select cnt = count(*)
from events x
where x.eventid <= e.eventid
) c
where c.cnt <= @max

select *
from @events
[/code]

Note : it will not work if you have events that is more than max. If you do have such data and with to ignore, you will need to modify the query to ignore such case.

KH
[spoiler]Time is always against us[/spoiler]

Torch
Starting Member

20 Posts

Posted - 2010-06-22 : 00:49:23

Thanks very much for that KH. However, will this approach work for larger sets of data. I only put a subset of the data in my previous post. In our real system there are thousands of records, hundreds of different eventid's, and we want to pick up 500 records at a time.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-22 : 02:15:04

give it a try and see how it perform

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources