0
declare
begin
  for i in (select aid ,address from address)
  loop
    for j in (select aid ,address from address )
    loop
      if i.address=j.address then
        if i.aid!=j.aid then
          update employee_add 
          set aid=i.aid 
          where aid=j.aid;
          delete from address 
          where aid=i.aid;
        end if;
      end if; 
    end loop;
  end loop;
end;
/

This code works fine as for loop. After that it shows error :------

*Cause: A foreign key value has no matching primary key value.
*Action: Delete the foreign key or add a matching primary key.

I have tables employee[eid (primary key) ,ename] ,address[aid (primary key),address],and many to many relation tableemployee_add[eid,aid]. Please help! Thank in Advance :)

APC
  • 144,005
  • 19
  • 170
  • 281
  • Using plain language, describe what problem you are trying to solve. – Littlefoot Feb 03 '19 at 06:31
  • actually i have 3 table address,employee and employee_address table where address table has address id and address name where address id is primary key where address is duplicate address like 101-banglore 102-banglore 103-banglore 104-pune 105-pune 106-hyderbad and employee_address has eid and aid such as 1-101,1-102,1-103,2-101,2-101,2-104,3-102,3-103.... i want to delete duplicate data from address having same address meanwhile i want to delete address associated with employee_address such that there should not be any data loss in employee_address table –  Feb 03 '19 at 12:21

1 Answers1

0

You can use only one loop statement and variables( v_address and v_aid ) to make a comparison between the rows as in the block below :

declare
  v_address address.address%type;
  v_aid     address.aid%type;  
begin
 for i in (select aid ,address from address order by aid)
 loop  
    if nvl(v_address,'')=i.address then 
         update employee_add set aid=v_aid where aid=i.aid;  
         delete address where aid=i.aid;
    else
       v_address := i.address;
       v_aid := i.aid;   
    end if;
  end loop;
end;
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
  • pls explain this ""if nvl(v_address,'')"" i am unable to understand this –  Feb 03 '19 at 14:53
  • @ViratSingh it's not suitable in some cases, especially for inequalities, collate directly two terms one of them is null, and the other is not null. And in the first step v_adress is still null . This is equality, but had better making this a habit for a developer I think. – Barbaros Özhan Feb 03 '19 at 14:55
  • can you tell me why my code only working for 1 time only because it gave the value for other loop 101 it worked fine but for other cases it showed me error like foreign key has no matching primary key i am newbie in pl sql so.. this can help me to understand it better . if you can help me out then it very good. btw thank you!! –  Feb 03 '19 at 15:33
  • @ViratSingh I retried the code exactly all the data which you supplied in the comment, and didn't see a problem. – Barbaros Özhan Feb 03 '19 at 16:21
  • it didn't worked when I kept the value null to my addresses in address table –  Feb 04 '19 at 06:27
  • @ViratSingh dear friend I cannot see the whole picture, but only the part shown to me and I can decide and answer depending on that part of the picture :) – Barbaros Özhan Feb 04 '19 at 08:58
  • 1
    with huge respect I am very happy and glad you helped me a lot thankyou sir ! hope if u had solved my problem I would have been safe for job .anyway thankyou sir –  Feb 04 '19 at 09:25