SQL single query update

I'm having to insert values into a new column in our database but I can't get my head around doing this in a consistent manner. There is a lot of data so doing anything manually is pretty much out of the question. Let me set the stage:

We have a table called Occurrence and a table called OccurenceBuckets where each occurrence is reference the bucket to which it has been assigned. Previously this was a one-way reference but for various reasons we have decided to add a reference back from the OccurrenceBucket to the first Occurrence (first in time, that is). The tables now look like this:

CREATE TABLE Occurrence
  OccurrenceID uniqueidentifier,
  OccurrenceBucketID uniqueidentifier,
  OccurrenceTime datetime,
  OccurrenceMessage nvarchar
  ...other meta data...

CREATE TABLE OccurrenceBucket
  OccurrenceBucketID uniqueidentifier,
  ...other meta data...
  FirstOccurrenceID uniqueidentifier,
  FirstOccurrenceTime datetime,
  FirstOccurrenceMessage nvarchar

I'm looking for a way to determine the first occurrence belonging to a bucket and assigning the FirstOccurrenceID, FirstOccurrenceTime and FirstOccurrenceMessage with values from this first occurrence for all my occurrencebuckets.

Do any of you sql-fu experts out there have the time 开发者_开发问答to help me out, all my attempts seen to produce incorrect or incomplete selection of occurrences.

You can try this

DECLARE @Occurrence TABLE(
        OccurrenceID INT,
        OccurrenceBucketID INT,
        OccurrenceTime DATETIME,
        OccurrenceMessage VARCHAR(MAX)
)

INSERT INTO @Occurrence (OccurrenceID,OccurrenceBucketID,OccurrenceTime,OccurrenceMessage)  
SELECT 1, 1, '01 Jan 2009', 'A'
INSERT INTO @Occurrence (OccurrenceID,OccurrenceBucketID,OccurrenceTime,OccurrenceMessage)  
SELECT 2, 1, '02 Jan 2009', 'B'
INSERT INTO @Occurrence (OccurrenceID,OccurrenceBucketID,OccurrenceTime,OccurrenceMessage)  
SELECT 3, 1, '03 Jan 2009', 'C'


INSERT INTO @Occurrence (OccurrenceID,OccurrenceBucketID,OccurrenceTime,OccurrenceMessage)  
SELECT 4, 2, '04 Jan 2009', 'D'
INSERT INTO @Occurrence (OccurrenceID,OccurrenceBucketID,OccurrenceTime,OccurrenceMessage)  
SELECT 5, 2, '05 Jan 2009', 'E'
INSERT INTO @Occurrence (OccurrenceID,OccurrenceBucketID,OccurrenceTime,OccurrenceMessage)  
SELECT 6, 2, '06 Jan 2009', 'F'

SELECT * FROM @Occurrence

DECLARE @OccurrenceBucket TABLE(
        OccurrenceBucketID INT,
        FirstOccurrenceID INT,
        FirstOccurrenceTime DATETIME,
        FirstOccurrenceMessage VARCHAR(MAX)
)

INSERT INTO @OccurrenceBucket (OccurrenceBucketID) SELECT 1
INSERT INTO @OccurrenceBucket (OccurrenceBucketID) SELECT 2

SELECT * FROM @OccurrenceBucket


UPDATE  @OccurrenceBucket
SET     FirstOccurrenceID = OccurrenceID,
        FirstOccurrenceTime = OccurrenceTime,
        FirstOccurrenceMessage = OccurrenceMessage
FROM    @OccurrenceBucket oc INNER JOIN
        (
            SELECT  o.*
            FROM    @Occurrence o INNER JOIN
                    (
                        SELECT  OccurrenceBucketID,
                                MIN(OccurrenceID) FirstOccurrenceID
                        FROM    @Occurrence
                        GROUP BY OccurrenceBucketID
                    ) Mins ON o.OccurrenceID = mins.FirstOccurrenceID
        ) Vals ON oc.OccurrenceBucketID = Vals.OccurrenceBucketID

SELECT * FROM @OccurrenceBucket

EDIT:

UPDATE  @OccurrenceBucket 
SET     FirstOccurrenceID = OccurrenceID, 
        FirstOccurrenceTime = OccurrenceTime, 
        FirstOccurrenceMessage = OccurrenceMessage 
FROM    @OccurrenceBucket oc INNER JOIN 
        ( 
                SELECT  o.* 
                FROM    @Occurrence o INNER JOIN 
                                ( 
                                        SELECT  OccurrenceBucketID, 
                                                        MIN(OccurrenceTime) FirstOccurrenceTime 
                                        FROM    @Occurrence 
                                        GROUP BY OccurrenceBucketID 
                                ) Mins ON o.OccurrenceTime = mins.FirstOccurrenceTime 
        ) Vals ON oc.OccurrenceBucketID = Vals.OccurrenceBucketID 

Well, to find the first occurrence in a bucket shouldn't the following work?

SELECT TOP 1
  OccurranceID, OccurranceTime, OccurranceMessage
FROM Occurance
WHERE
  OccurranceBucketID = @OccurranceBucketID
ORDER BY
  OccurranceTime ASC

You can assign the returned fields to variables and then update your OccurranceBucket accordingly.

^{NB: "occurrence" has no "a" in it.}

This answer relies on the OccurrenceTime being unique for each Occurrence: -

update OccBuck set  
OccBuck.FirstOccurrenceID = Occ.OccurrenceID,  
OccBuck.FirstOccurrenceTime = Occ.OccurrenceTime,  
OccBuck.FirstOccurrenceMessage = Occ.OccurrenceMessage  
from  
dbo.OccurrenceBucket as OccBuck  
inner join dbo.Occurrence as Occ on OccBuck.OccurrenceBucketID = Occ.OccurrenceBucketID  
inner join (select OccurrenceBucketID,  
MIN(OccurrenceTime) as 'MinOccurrenceTime'  
from dbo.Occurrence  
group by OccurrenceBucketID) as minOcc on Occ.OccurrenceBucketID=  minOcc.OccurrenceBucketID and  
Occ.OccurrenceTime = minOcc.MinOccurrenceTime;