Works perfectly, thanks.
Main Topics
Browse All TopicsHello, 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:
UPDATE mybuf
SET filename = A.filename
FROM tbl_MyBuffer mybuf
INNER JOIN (SELECT FirstName,LastName,DateOfB
FROM tbl_MyBuffer WHERE Len(isNull(fileName,'')) > 0
GROUP BY Firstname,Lastname,DateOfB
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() ?
Thanks!
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Business Accounts
Answer for Membership
by: ScottPletcherPosted on 2008-10-06 at 08:33:11ID: 22651090
UPDATE mybuf irth, StoreID,MAX(DateEntered) AS DateEntered irth,Store ID) A
SET filename = B.filename
FROM tbl_MyBuffer mybuf
INNER JOIN (SELECT FirstName,LastName,DateOfB
FROM tbl_MyBuffer WHERE fileName > ''
GROUP BY Firstname,Lastname,DateOfB
ON A.FirstName = mybuf.FirstName
AND A.lastName = mybuf.lastName
AND isnull(A.DateOfBirth,'') = isnull(mybuf.DateOfBirth ,'')
AND A.StoreID = mybuf.StoreID
INNER JOIN tbl_MyBuffer B
ON B.FirstName = A.FirstName
AND B.lastName = A.lastName
AND isnull(B.DateOfBirth,'') = isnull(A.DateOfBirth ,'')
AND B.StoreID = A.StoreID
AND B.DateEntered = A.DateEntered