Link to home
Start Free TrialLog in
Avatar of 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

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

I think this might be helpful for u
Avatar of tonyfuzzy


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.

Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

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