| Anders 2005-09-13, 8:25 pm |
| CREATE CURSOR rolhand (dos_id int, rol_datum Date, note char(4))
INSERT INTO Rolhand VALUES (1, DATE(2004,10,1), '111')
INSERT INTO Rolhand VALUES (1, DATE(2005,10,1), '222')
INSERT INTO Rolhand VALUES (1, DATE(2006,11,1), '333')
INSERT INTO Rolhand VALUES (2, DATE(2004,11,1), '444')
INSERT INTO Rolhand VALUES (2, DATE(2005,11,1), '555')
INSERT INTO Rolhand VALUES (2, DATE(2006,11,1), '666')
CREATE CURSOR Dossier (dos_id Int, roldatum Date, note Char(4))
INSERT INTO Dossier (dos_id) VALUES (1)
INSERT INTO Dossier (dos_id) VALUES (2)
UPDATE Dossier SET roldatum = Rolhand.rol_datum, note=Rolhand.note ;
FROM Rolhand ;
WHERE Rolhand.dos_id=Dossier.dos_id AND Rolhand.rol_datum>=;
(SELECT Min(Rolhand.rol_datum);
FROM Rolhand WHERE Rolhand.rol_datum > DATE(2005,9,13 ) )
result:
Dos_id Roldatum Note
1 2005-10-01 222
2 2005-11-01 555
If only the date needed updating you could use:
UPDATE Dossier SET Roldatum = ;
(SELECT MIN(rol_datum) FROM Rolhand ;
WHERE Rolhand.dos_id = Dossier.dos_id AND ;
Rolhand.rol_datum>DATE(2005,9,13))
Please use ANSI dates to avoid confusing American and European dates.
-Anders
using VFP9
"Marc de Vries" <marc.de.vries@zonnet.nl> skrev i meddelandet
news:efTSRRHuFHA.3188@TK2MSFTNGP14.phx.gbl...
> Table: ROLHAND
>
> *dos_id rol_datum note
> --------+-------------+----------
> 1 01-10-04 111
> 1 01-10-05 222
> 1 01-11-06 333
> 2 01-11-04 444
> 2 01-11-05 555
> 2 01-11-06 666
>
> Table: DOSSIER
>
> *rec_id roldatum note
> --------+-----------+---------
> 1 . .
> 2 . .
>
> For example the date is 13 sep 2005 (13-09-2005)
>
> I want to run some code that wil fill in all the first come rol_datum and
> the note belonging to this date in the dossier table.
>
> The first come date after 13 sept 2005 ( => 13 sept 2005)
>
>
> The result will be:
>
> Table: DOSSIER
>
> rec_id roldatum note
> --------+-----------+---------
> 1 01-10-05 222
> 2 01-11-05 555
>
>
|