Need help with my insert statement

I need an insert statement that will grab the maximum of TimeGenerated from the Events table and insert it into LastEventTime in the Host Table. But it has to be inserted based on the relationship of SystemNetName.

For Example,

Host Table
--------------------------------------
SystemNetName LastEventTime
Tweety 9/24/2004
Sylvester 9/25/2004

Events Table
----------------------------------------
SystemNetName TimeGenerated
Tweety 9/24/2004
Tweety 8/26/2004
Sylvester 8/21/2004
Sylvester 9/25/2004
Sylvester 7/5/2004


This is what I had so far but didn't work:
Insert into host(LastEventTime)
Select(Max(TimeGenerated) ) from Events Where
Events.SystemNetName = Host.SystemNetName


Any Ideas?

Something like this perhaps

I tried it and it didn't work. Primarily, I can't insert SystemNetName into the Host table because Host.SystemNetName is a primary key and Events.SystemNetName is the foreign key.

I pulled out the SystemNetName out of the 'Insert' and 'Select' and in the 'from' I did a left join on SystemNetName. Like this:



Logically it makes sense but I am getting an error: Cannot insert the value NULL into column 'SystemNetName'
Yet, I am not trying to insert into the SystemNetName column.

Thanks,
Laura

Laura,

What's you're reasoning in using an Insert? If there are new times for say "Tweety" always coming up, then you're trying to only keep 1 record for that NetName in your table, and simply "insert" the most recent date for that name, no? Sounds like you should be using an Update instead. Else, I think you have a flaw in the table design.

Yeah, I agree with you. But, don't I still need to insert the most recent time from the Events table into the Host table intially? Then after, I would do an update, correct?

The Events table keeps track of every event that occurs and never gets an update, just an insert and that works fine. Now, it is a matter of getting the most recent time and sticking it into the Host table, then from there the Host table is updated.

-Laura

Actually, I don't think I even need to have a LastEventTime field in the Host table. I am creating a web application and it might just be better to sql the Events table for the most recent time of each SystemNetName instead of doing an insert.

Thanks everybody.

-Laura