OraFAQ Forum: SQL & PL/SQL » Update table procedure through pl sql using double loop

Home » SQL & PL/SQL » SQL & PL/SQL » Update table procedure through pl sql using double loop (Oracle)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Update table procedure through pl sql using double loop [message #640498]

Wed, 29 July 2015 11:10

freestyle
Messages: 19
Registered: July 2015
Location: Bordeaux

Junior Member

Assuming that I have only one table : members_tbl which columns are :SN, FN, DB, IDDBL,FLAG, DBLCRIT I need to update the table if some conditions are OK with this algorithm :

ID_DUP := 1;
FOR (i in 1 to NumberOfrow(members_tbl)) THEN
{
     FOR (j in (i+1) to NumberOfrow(members_tbl)) THEN
   {
       IF(members_tbl(i).IDDBL IS NULL) THEN
             members_tbl(i).IDDBL := ID_DUP;
        IF (((members_tbl(i).DB ==members_tbl(j).DB) AND (members_tbl(i)SN == members_tbl(j).SN)) 
                AND (members_tbl(i).FN == members_tbl(j).FN)) 
                AND (members_tbl(j).IDDBL IS NULL)) THEN
              {
                      members_tbl(j).IDDBL := ID_DUP;
                      members_tbl(i).FLAG  := 1;
                      members_tbl(j).FLAG  := 1;
                      members_tbl(i).DBLCRIT:= 1;
                      members_tbl(j).DBLCRIT:= 1;
               }

    }
    ID_DUP := ID_DUP + 1;
}

Is there a way to write this algorithm in a pl/sql procedure ? I'm relatively new to PL/SQL and i've never written a stored procedure.
Thanks in advance.

Report message to a moderator

Re: Update table procedure through pl sql using double loop [message #640499 is a reply to message #640498]

Wed, 29 July 2015 11:40

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

And what is the algorithm in words or, better, the specification for this procedure?

Report message to a moderator

Re: Update table procedure through pl sql using double loop [message #640500 is a reply to message #640499]

Wed, 29 July 2015 12:15

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

how will you or I know when correct answer has been posted?

Report message to a moderator

Re: Update table procedure through pl sql using double loop [message #640505 is a reply to message #640499]

Wed, 29 July 2015 13:39

freestyle
Messages: 19
Registered: July 2015
Location: Bordeaux

Junior Member

Michel Cadot wrote on Wed, 29 July 2015 18:40

And what is the algorithm in words or, better, the specification for this procedure?

I need to find "potential duplicates" (not identical duplicates) from the table (due to typing errors); this algorithm works when I use it with a matrix through R for example on a small data quantity. But with a large dataset, It's too slow and it is necessary to exploit the server performance. This is only the first basic step I need because I'll add other conditions later(later inversion nam(surname), dateofbirth,...).
My knowledges on pl/sql are limited

Report message to a moderator

Re: Update table procedure through pl sql using double loop [message #640507 is a reply to message #640505]

Wed, 29 July 2015 13:44

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What are "potential duplicates"?

BlackSwan wrote on Wed, 29 July 2015 19:15

how will you or I know when correct answer has been posted?

Post a test case we can work with.

[Updated on: Wed, 29 July 2015 13:44]

Report message to a moderator

Re: Update table procedure through pl sql using double loop [message #640508 is a reply to message #640505]

Wed, 29 July 2015 14:08

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Are you looking for something like the Jaro-Winkler algorithm? Oracle does include that:
http://docs.oracle.com/database/121/ARPLS/u_match.htm#CHDDGFIC

Report message to a moderator

Re: Update table procedure through pl sql using double loop [message #640509 is a reply to message #640507]

Wed, 29 July 2015 14:42

freestyle
Messages: 19
Registered: July 2015
Location: Bordeaux

Junior Member

Michel Cadot wrote on Wed, 29 July 2015 20:44

What are "potential duplicates"?

BlackSwan wrote on Wed, 29 July 2015 19:15
how will you or I know when correct answer has been posted?

Post a test case we can work with.

Ok Here is the test case :
to be more comprensive, SN = SNAME, FN = FNAME, DB=DBIRTH

CREATE TABLE MEMBERS_TBL 
(
  IDM   NUMBER(9) NOT NULL
, SNAME VARCHAR2(20) 
, FNAME VARCHAR2(20) 
, DBIRTH VARCHAR2(15) 
, IDDBL NUMBER(10) 
, FLAG SMALLINT 
, DBLCRIT SMALLINT 
);

here are the data:

Insert into members_tbl (IDM,SNAME,FNAME,DBIRTH,IDDBL,FLAG,DBLCRIT) values (125,'BOLOREY','JEANNE','11/05/1955',NULL,NULL,NULL);
Insert into members_tbl (IDM,SNAME,FNAME,DBIRTH,IDDBL,FLAG,DBLCRIT) values (126,'BOLLOREY','JEANNE','11/05/1955',NULL,NULL,NULL);
Insert into members_tbl (IDM,SNAME,FNAME,DBIRTH,IDDBL,FLAG,DBLCRIT) values (153,'BALORE','GIANNE','11/05/1955',NULL,NULL,NULL);
Insert into members_tbl (IDM,SNAME,FNAME,DBIRTH,IDDBL,FLAG,DBLCRIT) values (223,'ABOLLOREYY','JEANNE','11/05/1955',NULL,NULL,NULL);
Insert into members_tbl (IDM,SNAME,FNAME,DBIRTH,IDDBL,FLAG,DBLCRIT) values (225,'FIABLE','MARINE','25/04/1963',NULL,NULL,NULL);
Insert into members_tbl (IDM,SNAME,FNAME,DBIRTH,IDDBL,FLAG,DBLCRIT) values (228,'FAIBLE','MARYANE','25/04/1963',NULL,NULL,NULL);
Insert into members_tbl (IDM,SNAME,FNAME,DBIRTH,IDDBL,FLAG,DBLCRIT) values (230,'FAIBLE','MARINE','25/04/1963',NULL,NULL,NULL);
Insert into members_tbl (IDM,SNAME,FNAME,DBIRTH,IDDBL,FLAG,DBLCRIT) values (235,'LIPARK','JACQLINE','20/12/1939',NULL,NULL,NULL);
Insert into members_tbl (IDM,SNAME,FNAME,DBIRTH,IDDBL,FLAG,DBLCRIT) values (236,'ILIPARC','JACQUELYNE','20/12/1939',NULL,NULL,NULL);
Insert into members_tbl (IDM,SNAME,FNAME,DBIRTH,IDDBL,FLAG,DBLCRIT) values (237,'LIPARC','JACQUELHINE','20/12/1939',NULL,NULL,NULL);
Insert into members_tbl (IDM,SNAME,FNAME,DBIRTH,IDDBL,FLAG,DBLCRIT) values (240,'RINTET','MALIKA','07/08/1954',NULL,NULL,NULL);
Insert into members_tbl (IDM,SNAME,FNAME,DBIRTH,IDDBL,FLAG,DBLCRIT) values (241,'GRISION','RAUGER','26/10/1931',NULL,NULL,NULL);
Insert into members_tbl (IDM,SNAME,FNAME,DBIRTH,IDDBL,FLAG,DBLCRIT) values (242,'GRISION','ROGER','26/10/1931',NULL,NULL,NULL);
Insert into members_tbl (IDM,SNAME,FNAME,DBIRTH,IDDBL,FLAG,DBLCRIT) values (245,'GRESION','RAUJER','26/10/1931',NULL,NULL,NULL);

There are some rows above that have to be merged by making the approximation of strings (through Jaro_Winkler in this case)and by checking the dates of birth.
These variables (IDDBL, FLAG, DBLCRIT) are used :
- To mark the similarity of character(IDDBL),
- To check that the box has already been verified(FLAG) if it is not duplicate, FLAG wille have NULL value,
- And to report The duplicate row has been identified(DBLCRIT)

Report message to a moderator

Re: Update table procedure through pl sql using double loop [message #640512 is a reply to message #640509]

Thu, 30 July 2015 00:19

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Is this in continuation to your previous question?

Report message to a moderator

Re: Update table procedure through pl sql using double loop [message #640521 is a reply to message #640512]

Thu, 30 July 2015 03:00

freestyle
Messages: 19
Registered: July 2015
Location: Bordeaux

Junior Member

Lalit Kumar B wrote on Thu, 30 July 2015 07:19

Is this in continuation to your previous question?

Hi Lalit,
Honestly the idea is almost the same, but you know that initially, the topic title was match/merge steps using Oracle Warehouse builder; after we discuss about the jw techniques but we were blocked about issuing the solution through sql; So it's why I wanted to see if there is an issue using some stored procedures(that I'm learning) above all by specifying the matching conditions that were not the same.

Report message to a moderator