>I have a table with null values in fields here and there. I would like a query that fills these in with values from the most recent record with something in that field. For example, if I have:
>

>field1     field2     field3
>
>3          5          6
>5          6
>7                     2
>                      9
>2          1          8
>3          2
>

>I want:
>

>field1     field2     field3
>
>3          5          6
>5          6          6
>7          6          2
>7          6          9
>2          1          8
>3          2          8
>

>
>Thank you.

You would need another column to identify the most recent record. Assuming from your example that the order is significant, you need a column called nrecno etc. Also, you want the query to fill in the data, but a query only selects data, doesn't update.

By the way, the first record cannot have any nulls.

The following syntax is SQL Server (except for the comments)
Hopefully you get the idea. So, can this be done with one sql select?
Yes, but with a multiple corelated subqueries that will not give the greatest performance.

select iif(ISNULL(a.field1), final.field1a, a.field1) as field1, ;
iif(ISNULL(a.field2), final.field2a, a.field2) as field2, ;
iif(ISNULL(a.field3), final.field3a, a.field3) as field3 ;
FROM ( ;

*-- build the final cursor here
*-- it should have one record with four fields, one for the recno that
*-- matches a.nrecno and each field populated from their own
*-- subqueries with the most recent value from a record less than
*-- the a.nrecno value

*-- the query to find field1a
*-- finds the maximum record with a record number less than
*-- the current a cursor record number with a value of course
*-- and returns this value which must then be used to join back
*-- to the table to find the field1a value for this record
*-- the following fields will show up in the table final

SELECT f1.nrecno, f1.field1a, f2.field2a, f3.field3a FROM

(SELECT nrecno, field1a = bb.field1
FROM
(SELECT (maxrecno = max(b.nrecno)
FRIN mytable b ;
WHERE b.nrecno < a.nrecno and ;
NOT ISNULL(b.field1)) as field1recno, mytable bb
WHERE field1recno.maxnrecno = bb.nrecno)
as f1

(SELECT nrecno, field2a = bb.field2
FROM
(SELECT (maxrecno = max(b.nrecno)
FRIN mytable b ;
WHERE b.nrecno < a.nrecno and ;
NOT ISNULL(b.field2)) as field2recno, mytable bb
WHERE field2recno.maxnrecno = bb.nrecno)
as f2

(SELECT nrecno, field3a = bb.field3
FROM
(SELECT (maxrecno = max(b.nrecno)
FRIN mytable b ;
WHERE b.nrecno < a.nrecno and ;
NOT ISNULL(b.field3)) as field31recno, mytable bb
WHERE field3recno.maxnrecno = bb.nrecno)
as f3
WHERE f1.nrecno = f2.nrecno AND
f2.nrecno = f3.nrecno ) as final, mytable a
WHERE a.nrecno = final.nrecno

*-- note that the 'a' alias referenced at the top only shows up at the bottom where it is one of the tables from the first FROM clause

The syntax may not be entirely correct (brackets, etc) but this kind of query is very powerful and does work.