T-Sql update join query
Posted on 2008-10-06
Hello, I have rows I enter into an intermediate table and perform some operations, in the final process I end up inserting into the main table one row per each entity defined as same firstname, lastname, storenumber and dateofbirth. Before I do that, I need to update a field called fillename with the value of the filename based on the max dateentered field for that entity, if the fiilename field is not null. It is possible that all rows for the entity have null for filename.
Assuming the rows of data below in my intermediate table, I want to update the filename field all 3 to the filename field to 'blat.jpg'
FirstName LastName DOB StoreID Filename DateEntered
John Doe 10/1/1966 55 blah.jpg 10/1/2007
John Doe 10/1/1966 55 blat.jpg 5/11/2008
John Doe 10/1/1966 55 NULL 9/01/2008
Soething like this, but adding the grouping or ranking by datenetered and adding the filename field to query:
SET filename = A.filename
FROM tbl_MyBuffer mybuf
INNER JOIN (SELECT FirstName,LastName,DateOfBirth, StoreID,DateEntered
FROM tbl_MyBuffer WHERE Len(isNull(fileName,'')) > 0
GROUP BY Firstname,Lastname,DateOfBirth,StoreID) A
ON A.FirstName = mybuf.FirstName
AND A.lastName = mybuf.lastName
AND isnull(A.DateOfBirth,'') = isnull(mybuf.DateOfBirth ,'')
AND A.StoreID = mybuf.StoreID
Since I am using SQL 2005 would this lend itself to rank() ?