Identify phone numbers in a PL/SQL iteration

Question

I would like to update a table (T_NUMBERS) which has a PHONE_NUMBER and a COUNTRY columns. The COUNTY col is empty (all NULL), The phone numbers are not unique and their format: "420575757111" (starts with the country code without + or 00). The international calling codes are stored in another table (INT_CODES), two columns: INT_CODE, COUNTRY. Looks like this:

COUNTRY     ||   INT_CODE
-------------------------
USA/CANADA  ||  1
RUSSIA      ||  7
EGYPT       ||  20
GREECE      ||  30
BELGIUM     ||  32
FRANCE      ||  33
HUNGARY     ||  36
COLOMBIA    ||  57
KENYA       ||  254
IRELAND     ||  353
GRENADA     ||  1473

and so on.

My concept is to create a script which works in the following way:

In the first round compares the first 4 digits of the phone numbers to INT_CODE and update the T_NUMBERS.COUNTRY fields with INT_CODES.COUNTRY where it founds a match, something like this:
```
INT_CODES.INT_CODE = SUBSTR(T_NUMBERS.PHONE_NUMBER,1,4)
```
thereafter the second round compares the first 3 digits where the T_NUMBERS.COUNTRY is still NULL.
in the next two rounds check the 2 and the 1 digit codes in the same way in order to fill the all country field but to not override the already filled ones.

My problem is I couldn't run a single script which would execute at least the first step because Oracle seemingly does not support the JOIN in UPDATE statements as you can read here for example:

Update statement with inner join on Oracle

and I tried the solutions from the answer, but it doesn't work:

SQL Error: ORA-01427: single-row subquery returns more than one row

After that I tried this (only the first round):

begin
for t_rows in c_code
loop

    update (select TN.COUNTRY as C_OLD, IC.COUNTRY as C_NEW from T_NUMBERS TN
    left join INT_CODES IC on IC.INT_CODE = substr(TN.PHONE_NUMBER,1,4) where
    TN.COUNTRY IS NULL) T_TAB

    set TAB.C_OLD = TAB.C_NEW;
close c_code;

end loop;

Error message: ORA-06512: at line 8 01779. 00000 - "cannot modify a column which maps to a non key-preserved table"

So my main question is: what statement I should insert into a loop? My side-question: Is there any other solution to produce the same result (without creating further tables or modifying the structure of the existing ones)?

Thanks in advance.

San · Answer 1 · 2015-09-16T11:34:17.127

What is the need of a loop here? You have to re-write your update statement to something like this:

UPDATE t_numbers tn
   SET tn.country = (SELECT ic.country
                       FROM int_codes ic
                      WHERE ic.int_code = substr(tn.phone_number, 1, 4))
 WHERE tn.country is null
   AND EXISTS (SELECT 1
                 FROM int_codes ic
                WHERE ic.int_code = substr(tn.phone_number, 1, 4));

Then repeat the same for 3, 2, and 1 as follows(for 3):

UPDATE t_numbers tn
   SET tn.country = (SELECT ic.country
                       FROM int_codes ic
                      WHERE ic.int_code = substr(tn.phone_number, 1, 3))
 WHERE tn.country is null
   AND EXISTS (SELECT 1
                 FROM int_codes ic
                WHERE ic.int_code = substr(tn.phone_number, 1, 3));

UPDATE:

You can also loop through 4 to 1 to achieve the task

  begin
    for i in 1..4 loop
      UPDATE t_numbers tn
       SET tn.country = (SELECT ic.country
                           FROM int_codes ic
                          WHERE ic.int_code = substr(tn.phone_number, 1, (5-i)))
     WHERE tn.country is null
       AND EXISTS (SELECT 1
                     FROM int_codes ic
                    WHERE ic.int_code = substr(tn.phone_number, 1, (5-i)));
    end loop;
  END;

I tried both solutions above, but the error message is still the same: — A117, Sep 16 '15 at 12:03
That means there are multiple entries in int_codes table for a given int_code. check that and filter the data according to it. You may need to put more condition in inner sub query's where condition. — San, Sep 16 '15 at 13:11
Ok, both worked fine, you were right, the problem were the call codes belonged to two different countries. I accepted Nagh's version finally, because it was more convenient, but I liked yours as well. Thank you guys! — A117, Sep 16 '15 at 15:58

Nagh · Accepted Answer · 2015-09-16T20:17:29.197

1

I'd say its quite hard to find another sql-engine more feature rich then oracle. I advice you to setup sqlfiddle for question like this.

UPDATE t_numbers tn SET
  tn.country = (
    SELECT ic.country 
    FROM int_codes ic 
    WHERE ic.int_code = substr(tn.phone_number, 1, 4)
  )
WHERE tn.country IS NULL;

if you running it as pl/sql script you can wrap it with loop, to avoid copy/paste.

BEGIN
  FOR i IN REVERSE 1..4
  LOOP
    UPDATE t_numbers tn SET
      tn.country = (
        SELECT ic.country 
        FROM int_codes ic 
        WHERE ic.int_code = substr(tn.phone_number, 1, i)
      )
    WHERE tn.country IS NULL;
  END LOOP;
END;

This code relies on a fact that column int_code in the int_codes table is UK.

edited Sep 16 '15 at 20:17

answered Sep 16 '15 at 11:30

Nagh

1,757
1
14
19

See my reaction under San's answer. Thank you! – A117 Sep 16 '15 at 15:59
Thank you. I'll update answer to reflect this problem too. – Nagh Sep 16 '15 at 20:13

Identify phone numbers in a PL/SQL iteration

2 Answers2

Linked