Visual FoxPro SQL Queries

Author

replace

Marc de Vries

2005-09-09, 7:24 am

MAXDATUM

rol_id max_datum
-------+------------
1 01-01-03
2 03-03-04

====================
====================
================

DOSSIER

rol_id cost_code max_datum
---------+-------------+-----------
1 marc <---- Here
2 piet <---- Here

I want to make an query that filles in DOSSIER.max_datum from the MAXDATUM
table.

I think that i must use an replace?

borisb

2005-09-09, 7:24 am

Marc de Vries wrote:
>
> MAXDATUM
>
> rol_id max_datum
> -------+------------
> 1 01-01-03
> 2 03-03-04
>
> ====================
====================
================
>
> DOSSIER
>
> rol_id cost_code max_datum
> ---------+-------------+-----------
> 1 marc <---- Here
> 2 piet <---- Here
>
> I want to make an query that filles in DOSSIER.max_datum from the MAXDATUM
> table.
>
> I think that i must use an replace?
>
>
>

Two ways:
1.
*** MaxDatum must be indexed by Rol_Id and this order must be current
SELECT Dossier
SET RELATION TO Rol_Id INTO MaxDatum
REPLACE Max_Datum WITH MaxDatum.Max_Datum ALL

2.
** No need any additional indexes or relations
UPDATE Dossier SET Max_Datum = MaxDatum.Max_Datum WHERE Dossier.Pol_Id
== MaxDatum.Pol_Id

Anders

2005-09-09, 8:25 pm

In VFP9 you can use the UPDATE command which has been enhanced to Standard
SQL form.
UPDATE Dossier SET max_datum = ;
(SELECT max_datum FROM Maxdatum ;
WHERE Dossier.rol_id = Dossier.rol_id )

VFP9 also supports the MSSQL Server FROM clause in UPDATE
UPDATE Dossier SET max_datum = Maxdatum.max_datum ;
FROM Maxdatum WHERE Maxdatum.rol_id=Dossier.rol_id

Before VFP9 you could mix SQL and Xbase
USE Maxdatum IN 0
UPDATE Dossier SET max_datum = LOOKUP(maxdatum.max_datum, dossier.rol_id,
maxdatum.rol_id)

-Anders

"Marc de Vries" <marc.de.vries@zonnet.nl> skrev i meddelandet
news:%23qZC15RtFHA.2756@TK2MSFTNGP09.phx.gbl...
> MAXDATUM
>
> rol_id max_datum
> -------+------------
> 1 01-01-03
> 2 03-03-04
>
> ====================
====================
================
>
> DOSSIER
> rol_id,
> rol_id cost_code max_datum
> ---------+-------------+-----------
> 1 marc <---- Here
> 2 piet <---- Here
>
> I want to make an query that filles in DOSSIER.max_datum from the MAXDATUM
> table.
>
> I think that i must use an replace?
>
>

Sponsored Links