Any efficient solution for that

Question

  declare 


       cursor cur1 is select * from address where aid in
        (select Min(aid) from address group by
        country,state,city,street_name,locality,house_no);

       cursor cur2 is select * from address;


        cur1_aid                address.aid%type;
        cur1_country            address.country%type;
        cur1_city               address.city%type;
        cur1_state              address.state%type;
        cur1_streetAddress      address.street_name%type;
        cur1_locality           address.locality%type;
        cur1_houseNo            address.house_no%type;


        cur2_aid                address.aid%type;
        cur2_country            address.country%type;
        cur2_city               address.city%type;
        cur2_state              address.state%type;
        cur2_streetAddress      address.street_name%type;
        cur2_locality           address.locality%type;
        cur2_houseNo            address.house_no%type;



begin 
         open cur1;
            loop
            fetch cur1 into cur1_aid,cur1_country,cur1_state,cur1_city,cur1_streetAddress,cur1_locality,cur1_houseNo;
            exit when cur1%NOTFOUND;
                open cur2;
                    loop
                    fetch cur2 into  cur2_aid,cur2_country,cur2_state,cur2_city,cur2_streetAddress,cur2_locality,cur2_houseNo;
                    exit when cur2%NOTFOUND;
                        if(cur1_country=cur2_country) and (cur1_state=cur2_state) and (cur1_city=cur2_city) and (cur1_streetAddress=cur2_streetAddress) and (cur1_locality=cur2_locality) and (cur1_houseNo=cur2_houseNo) then
                            if (cur1_aid!=cur2_aid) then
                                    update employee_add set aid=cur1_aid where aid=cur2_aid;
                                    delete address where aid=cur2_aid;
                            end if;
                        end if;
                    end loop;
                close cur2;
            end loop;
        close cur1;
    DELETE FROM employee_add a
    WHERE ROWID > (SELECT MIN(ROWID) FROM employee_add b
    WHERE b.eid=a.eid and b.aid=a.aid
    );
    end;
    /

I have three table Employee(eid,ename) ,Address(aid,country,state,city,streetaddress,locality,houseNo) and a relationship table (M2M) MANY TO MANY TABLE employee_add(eid,aid),

I want to remove duplicates from address table and employee_add table without data loss

So this is based on [the answer to your previous question on the subject](https://stackoverflow.com/q/54500413/146325). (Yes, deleting your user account didn't delete your question.) What is wrong with that solution? Please define what you mean by *"efficient solution"*. — APC, Feb 06 '19 at 14:55
Actually someone told me that this will work but this code is not very efficient any idea how this can be improved .... — Virat Singh, Feb 06 '19 at 15:30

score 0 · Answer 1 · answered Feb 26 '19 at 20:36

Assuming this is a one time de-duplication you could:

Create a new temporary set of eid <-> aid relationships based on the current address attached to an employee and always pick the min address record with matching data (this is what you are doing above)
Delete existing eid <-> aid relationships
Insert new relationships from step 1, drop step 1 data
Delete addresses that no longer have any employee attached

Something like this (untested as you did not provide any DDL or DML to create a working example from):

-- Step 1
CREATE TABLE employee_add_new AS
  SELECT ea.eid,
         (SELECT MIN(a2.aid)
            FROM address a2
           WHERE a2.country = a.country
             AND a2.state = a.state
             AND a2.city = a.city
             AND a2.street_name = a.street_name
             AND a2.locality = a.locality
             AND a2.house_no = a.house_no) AS aid
    FROM employee_add ea
   INNER JOIN address a
      ON a.aid = ea.aid;

-- Step 2
TRUNCATE TABLE employee_add;

-- Step 3
INSERT INTO employee_add
  (eid,
   aid)
  SELECT eid,
         aid
    FROM employee_add_new;

DROP TABLE employee_add_new;

-- Step 4
DELETE FROM address a
 WHERE NOT EXISTS (SELECT NULL
          FROM employee_add ea
         WHERE ea.aid = a.aid);

You could also change step 2 and 3 to drop the existing employee_add table and rename employee_add_new to employee_add, but I have no idea what your table structure looks like (columns, FKs, indexes, etc).

Any efficient solution for that

1 Answers1