SQL Select Duplicate Values-CodePudding

I have the following database structure Tables in the database

If a user from USER_TABLE has duplicate data in the ADDRESS_TABLE table and they are both active, one of the ACTIVE records should be changed to 0. Examples of duplicate data are highlighted in red. That is, in the ADDRESS_TABLE table, you need to change the activity of one of the records with numbers 14 or 15 and 18 or 19. And in the DOCUMETNS table, the documents related to the records from the table above are record 2033 or 1400 and record 3000 or 3001. Each record in the ADDRESS_TABLE table and the DOCUMETNS table has a common INFID - a table with data about the recording time. I tried to do this, but I get an extra value.

SELECT DISTINCT at.INFID, ut.UID FROM
  ADDRESS_TABLE at
  INNER JOIN users_table ut on ut.UID = at.UID
  INNER JOIN DOCUMENTS doc on ut.UID = doc.UID
  WHERE
ut.rowid <>
(
  SELECT
    MAX(ad.rowid)
  FROM
    ADDRESS_TABLE ad
    WHERE

    at.HOME = ad.HOME AND
    at.APP = ad.APP AND
    at.INDEX = ad.INDEX AND  
    at.ACTIVE = 1 and ad.ACTIVE = 1 HAVING count(*) > 1


)   ORDER BY ut.UID;

CodePudding user response：

You can use EXISTS to check whether another active row exists with the same values.

update address_table a
set active = 0
where active = 1
and exists
(
  select null
  from address_table other
  where other.uid = a.uid
  and decode(other.home, a.home, 'same', 'different') = 'same'
  and decode(other.app, a.app, 'same', 'different') = 'same'
  and decode(other.index, a.index, 'same', 'different') = 'same'
  and other.active = 1
  and other.id > a.id 
);

update documents d
set active = 0
where active = 1
and exists
(
  select null
  from documents other
  where other.uid = d.uid
  and decode(other.contract_type, d.contract_type, 'same', 'different') = 'same'
  and other.active = 1
  and other.docid > d.docid 
);

commit;

I am using DECODE here to include nulls in my equality check. If you want standard SQL instead, make this and (other.home = a.home or (other.home is null and a.home is null)) etc. If the column is not nullable, then and other.home = a.home suffices and should be preferred.

I am updating all active rows where an active sibling with a higher ID exists. If you want to make this "where an active sibling with a higher INFID exists", then just change the two lines.