| Anders 2005-07-11, 1:25 pm |
| UPDATE Table1 SET error='01' ;
WHERE emprole='E' AND NOT EXISTS ;
(SELECT * FROM Table1 AS Table2 ;
WHERE emprole='S' ;
AND Table1.groupid=Table2.groupid)
UPDATE Table1 SET error='02' ;
WHERE emprole='S' AND NOT EXISTS ;
(SELECT * FROM Table1 AS Table2 ;
WHERE emprole='E' ;
AND Table1.groupid=Table2.groupid)
-Anders
Den 05-07-08 18.17, i artikeln uPUd6h9gFHA.3616@TK2MSFTNGP12.phx.gbl, skrev
"Sunny" <sunny_1178@hotmail.com>:
> 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.
>
>
>
|