Link to home
Start Free TrialLog in
Avatar of tonyfuzzy
tonyfuzzy

asked on

Fill Blank Fields With Data

I have a database with fields "ID" and "DocName" that is sorted by ID.
A sample of the record data is:
1, OneDoc
2, null
3, null
4, null
5, AnotherDoc
6, null
7, null
8, null
9, YetAnotherDoc
10, null.

I would like to autofill the records with the following results:

1, OneDoc
2, OneDoc
3, OneDoc
4, OneDoc
5, AnotherDoc
6, AnotherDoc
7, AnotherDoc
8, AnotherDoc
9, YetAnotherDoc
10, YetAnotherDoc.

Any Help Would be greatly appreciated.
Avatar of divyeshhdoshi
divyeshhdoshi

update tablename t1
set DocName=(select 1 docname from tablename t2 where t2.id<t1.id and t2.DocName is not null order by t2.id desc) where DocName is null


I think this might be helpful for u
Avatar of tonyfuzzy

ASKER

Does this mean i need two tables?
Hi Tony,

I think divyeshhdoshi is suggesting that rather than trying to modify the retrieved data to meet your requirements, you change the data in your table so that your select statement will show you the results you want.

You say "I have a database with fields "ID" and "DocName" that is sorted by ID", assuming these two columns exist in the same table (call it Documents), your select statement might look something like the following:

select ID, DocName
from Documents
order by ID

Your problem is that some of the rows in this table don't have a value for DocName.  What you can do is to "clean" your data to ensure all Documents do have a DocName using the update statement supplied by divyeshhdoshi.

This will then allow your select statement to retrieve the results you require.

I would point out that it doesn't seem like good relational design for an column to "assume" a value based on the order it is retrieved from a table, it would be far better if all rows inserted into the table always had a the required DocName.

Hope this helps.

ASKER CERTIFIED SOLUTION
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Many Thanks