nested select and update

[bluebytez]

Hi, I have a problem with a database which I have imported from a text file.
In one of my tables, there are more a few rows for each staff. The problem is, the data for one of the columns ( i.e. Age ) only exists in the first row of the same staff. How do I make a SQL statement to fill the other rows belonging to the same staff with the data from the first row ?
I hope I have not confused anyone.
Thanks for your help !

 

[tlbroadbent]

Here is one possibility that may work depending on your RDMS. You may need to modify the Where criteria depending on the contents of the Age column.

Update table
Set Age = b.Age
From table As a
Inner Join
(Select StaffId, Age From table
Where Age Is Not Null) As b
On a.StaffID=b.StaffID
Where a.Age Is Null Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[bluebytez]

Thanks a lot tlbroadbent, it worked for me !
You are the SQL MAESTRO !