SQL single query update

Question

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.

Answer 1

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