FoxPro Help and Support - how do I update fileds based on other records in same table

Author

how do I update fileds based on other records in same table

Sunny

2005-07-08, 11:24 am

I am using VFP 8.0. How do I update error field in my table depends on
criteria match on other records in same table with UPDATE-SQL?

Here is exaample
GrpId EmpId EmpRole Error
1 1 S
1 2 E
1 3 E
2 4 S
3 5 E

Now I want to update error ('01') for records who has no employee(s)
(EmpRole = 'E') for records having EmpRole 'S' (supervisor) in same group
(GrpId) also want to update
error ('02') for records who has no supervisor (EmpRole = 'S') for records
having EmpRole 'E' (employee) in same group (GrpId)

Result should be:
GrpId EmpId EmpRole Error
1 1 S
1 2 E
1 3 E
2 4 S 01
3 5 E 02

Can I do this with one UPDATE SQL query?

Thanks.

Vasim

2005-07-10, 8:24 pm

If you have to use UPDATE SQL query I am not sure exactly how to do that in
VFP8, however, in VFP9 you can use UPDATE FROM (correlated update).....

VFP9 Example:

UPDATE YourTable;
SET Error = icase(EmpRole = 'E','01',EmpRole <> 'S','02','03') ;
FROM YourTableAlias;
WHERE YourTableA.GrpID = YourTableAlias.GrpID;
AND YourTableA.EmpID= YourTableAlias.EmpID

Also in VFP8 or VFP9 you might want to create a FUNCTION that uses a SEEK()
and returns the Error Code '01','02' etc. and then do an UPDATE YouTable SET
Error = YourNewFunc(GrpID, EmpID) etc. I am not sure I am helping here sorry
:(

"Sunny" wrote:

> I am using VFP 8.0. How do I update error field in my table depends on
> criteria match on other records in same table with UPDATE-SQL?
>
> Here is exaample
> GrpId EmpId EmpRole Error
> 1 1 S
> 1 2 E
> 1 3 E
> 2 4 S
> 3 5 E
>
>
> Now I want to update error ('01') for records who has no employee(s)
> (EmpRole = 'E') for records having EmpRole 'S' (supervisor) in same group
> (GrpId) also want to update
> error ('02') for records who has no supervisor (EmpRole = 'S') for records
> having EmpRole 'E' (employee) in same group (GrpId)
>
> Result should be:
> GrpId EmpId EmpRole Error
> 1 1 S
> 1 2 E
> 1 3 E
> 2 4 S 01
> 3 5 E 02
>
> Can I do this with one UPDATE SQL query?
>
> Thanks.
>
>
>
>

Olaf Doschke

2005-07-11, 7:24 am

I don't see how to do this in a single Update quer without the
possibilities vfp9 offers.

This Select may help you finding the erroneous groups:

Select;
GrpID,;
Sum(IIf(EmpRole="E",1,0)) As nEmployees,;
Sum(IIf(EmpRole="S",1,0)) As nSupervisors;
from <YourTable>;
group by GrpID;
having (nEmployees=0 or nSupervisors=0)

scanning through the result you can update the
groups with the appropriate error.

Bye, Olaf.

Olaf Doschke

2005-07-11, 7:24 am

Another thing comes to my mind:

Update <yourTable> Set Error="01" where grpID IN (Select;
GrpId;
from <yourTable>;
group by grpID;
having (sum(iif(EmpRole="E",1,0))=0) and (sum(iif(EmpRole="S",1,0))>0) )

and

Update <yourTable> Set Error="02" where grpID IN (Select;
GrpId;
from <yourTable>;
group by grpID;
having (sum(iif(EmpRole="S",1,0))=0) and (sum(iif(EmpRole="E",1,0))>0) )

This works even with VFP7, but I see no way of combining them to one Select.

Bye, Olaf.

Olaf Doschke

2005-07-11, 7:24 am

Hi Sunny,

finally got it, but only with the assumption you only have
EmpRole "S" or "E".

Update <yourTable>;
Set Error=IIf(EmpRole='S
','01','02');
Where GrpID IN;
(Select GrpID;
From <yourTable>;
Group by GrpID;
Having (Sum(IIf(EmpRole='E'
,1,0))=0) Or (Sum(IIf(EmpRole='S'
,1,0))=0);
)

*-------------

If you have other Roles this may be sufficient, but may not be,
as you may have groups with another EmpRole only, which wouldn't
be marked erroneous:

Update <yourTable>;
Set Error=IIf(EmpRole='S
','01',IIf(EmpRole='
E','02',' '));
Where GrpID IN;
(Select GrpID;
From <yourTable>;
Group by GrpID;
Having (Sum(IIf(EmpRole='E'
,1,0))=0) Or (Sum(IIf(EmpRole='S'
,1,0))=0);
)

Bye, Olaf

Sunny

2005-07-12, 8:25 pm

Thanke Anders and Olaf, I will give a try, sounds great.
"Sunny" <sunny_1178@hotmail.com> wrote in message
news:uPUd6h9gFHA.3616@TK2MSFTNGP12.phx.gbl...
> I am using VFP 8.0. How do I update error field in my table depends on
> criteria match on other records in same table with UPDATE-SQL?
>
> Here is exaample
> GrpId EmpId EmpRole Error
> 1 1 S
> 1 2 E
> 1 3 E
> 2 4 S
> 3 5 E
>
>
> Now I want to update error ('01') for records who has no employee(s)
> (EmpRole = 'E') for records having EmpRole 'S' (supervisor) in same group
> (GrpId) also want to update
> error ('02') for records who has no supervisor (EmpRole = 'S') for records
> having EmpRole 'E' (employee) in same group (GrpId)
>
> Result should be:
> GrpId EmpId EmpRole Error
> 1 1 S
> 1 2 E
> 1 3 E
> 2 4 S 01
> 3 5 E 02
>
> Can I do this with one UPDATE SQL query?
>
> Thanks.
>
>
>

Sponsored Links