maephisto maephisto - 1 month ago 8
C# Question

Oracle update duplicate rows in a table

I have an Oracle table called users, with columns

name, location, organization, valid_from, valid_to, active.


What I am trying to do is:
if there are two records with same name, location, organization but with valid_to, valid_from intervals overlapping, to set the active status of each record to 'NOK'.
Here's how I am trying to do it :

UPDATE table SET active= 'NOK'
WHERE
(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) ;


But this doesn't seem to do what I desire. What am I doing wrong?

Answer

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!)