I have an Oracle table called users, with columns
name, location, organization, valid_from, valid_to, active.
UPDATE table SET active= 'NOK'
(name, location, organization) IN (
SELECT t1.name, t1.location, t1.organization
FROM table t1
WHERE (valid_from > t1.valid_from and valid_to < t1.valid_to )
GROUP BY t1.name, t1.location, t1.organization
HAVING COUNT(*) > 1) ;
The WHERE clause in your subquery will be FALSE for all rows because you didn't specify a table alias for some columns:
(valid_from > t1.valid_from and valid_to < t1.valid_to )
will be assumed to mean
(t1.valid_from > t1.valid_from and t1.valid_to < t1.valid_to )
I think this does what you want:
UPDATE table t1 SET active = 'NOK' WHERE EXISTS ( SELECT 1 FROM table t2 WHERE t2.name = t1.name AND t2.location = t1.location AND t2.organization = t1.organization AND t1.valid_from <= t2.valid_to AND t2.valid_from <= t1.valid_to AND t1.ROWID != t2.ROWID );
However, if valid_from or valid_to can be null then you need to handle that case also.
(The ROWID comparison is to prevent tows matching with themselves!)