Re: Newbie: Help writing a sproc

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 01/14/05 

Date: Fri, 14 Jan 2005 22:01:12 +0100

On Fri, 14 Jan 2005 08:57:01 -0800, John wrote:

>the first one didn't update any of the rows

Hi John,

Are you sure? I just tested it and it works fine for me. Here's the script
I ran, using the test data you posted earlie (changing the time on one row
to see that data is not set to NULL if there is a recent last_Used)

create table Test (last_Used datetime, User_ID int)
go
insert Test
select '2005-01-14T13:59:06', 1 union all
select '2005-01-14T15:50:09', 2 union all
select '2005-01-14T13:59:40', 1 union all
select '2005-01-14T13:59:55', 2 union all
select '2005-01-14T21:11:09', 2
go
UPDATE Test
SET User_ID = NULL
WHERE (SELECT MAX(last_Used)
       FROM Test AS T
       WHERE T.User_ID = Test.User_ID) < DATEADD(hour, -1, getdate())
go
select * from Test
go
drop table Test
go

Did you use copy and paste when you tested my query? Did you check for any
typos? If you're sure you didn't make a mistake in copying the query,
could you tell me the output of SELECT @@VERSION?

>and the second one has a syntax
>error in line 6(which I'll try to discover).

I missed a closing parenthesis. Add one ) and it runs fine.

>Fransceco gave me a query that
>worked, Thanks very much for you help.

Maybe I misunderstood your requirements. My tests show that Fransesco's
query will set the userid to NULL if the userid has at least one entry in
the table that's over an hour old. So if userid 3 has an entry of 70
minutes ago and an entry of 5 minutes ago, they will both be set to NULL.
If that is what you want, then I misunderstood your requirements.

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages

  • Re: SQL query question #2
    ... and having test data makes it easier to test. ... Hugo Kornelis, SQL Server MVP ... My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis ... E.g. if I query the above data with required values of 'b' and 'd'. ...
    (comp.databases.ms-sqlserver)
  • Re: more than one set of records separated by blank lines
    ... trigger on the last entry instead. ... Please try posting a complete program if you are having more trouble. ... the special file handle to include test data in a test program. ...
    (comp.lang.perl.misc)
  • Re: How do I empty a table?
    ... You could run a delete query: ... I have been known to open the tables, and select and delete all test data. ... run compact and repair. ... How do I empty the database of the test data when I've finished testing ...
    (microsoft.public.access.gettingstarted)
  • RE: Repost - help to merge 2 queries
    ... I will be interested if it works for more than the test data. ... 1st query with inner joins between table A and B ... I've quickly read the reply from John Spencer and what he has written lends ...
    (microsoft.public.access.queries)
  • Re: Combo values based on query
    ... Thanks (The query won't always return a single ... netry - it's just that I had a small amount of test data. ... Try using the OnCurrent event of the form to Requery the Combo box. ... consider using a caculated field, ...
    (microsoft.public.access.formscoding)