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

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.}

Answer 2

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

Answer 3

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