CODEWITHSUNDEEP
oracle-database
RAS RASS
RAS RASS

Reputation: 37

Insert New Value from another field value in the same row (using oracle database)

I have two tables (MST_IP and MST_ROUTER) like this below:

MST_IP 

SEQ          IP_NUMBER          STATUS
1            12.123.0.0         0
2            12.123.0.1         0
3            12.104.0.1         0
4            12.104.0.2         0
5            13.123.0.1         0
6            13.123.0.2         0
7            11.555.8.1         0
8            11.555.8.2         0

I want to insert IP_NUMBER from MST_IP to MST_ROUTER (IP_NUMBER and REMOTE_NUMBER). What I want the data will be like this below.

MST_ROUTER

USER_ID          IP_NUMBER          REMOTE_NUMBER
AA               12.123.0.0          12.123.0.1
BB               12.104.0.1          12.104.0.2
CC               13.123.0.1          13.123.0.2
DD               11.555.8.1          11.555.8.2

below is my code. 

/* Formatted on 9/28/2017 11:44:11 AM (QP5 v5.115.810.9015) */ SET SERVEROUTPUT ON

DECLARE    INS     VARCHAR2 (100);    INS2    VARCHAR2 (100);    SEQ_R INTEGER; BEGIN    SEQ_R := 1;

   SELECT   IP_NUMBER
     INTO   INS
     FROM   (  SELECT   *
                 FROM   MST_IP
                WHERE   STATUS = 0
             ORDER BY   SEQ ASC)
    WHERE   ROWNUM = 1;

   SELECT   IP_NUMBER
     INTO   INS2
     FROM   (  SELECT   *
                 FROM   MST_IP
                WHERE   STATUS = 0 AND SEQ = SEQ_R + 1
             ORDER BY   SEQ ASC)
    WHERE   ROWNUM = 1;

   INSERT INTO MST_ROUTER (USER_ID, IP_NUMBER)
     VALUES   ('HMJ-BKS', INS);

   COMMIT;

   UPDATE   MST_IP
      SET   STATUS = 1
    WHERE   IP_NUMBER = INS;

   COMMIT;

   UPDATE   MST_ROUTER
      SET   REMOTE_NUMBER = INS2
    WHERE   USER_ID = 'HMJ-BKS';

   COMMIT;

   UPDATE   MST_IP
      SET   STATUS = 1
    WHERE   IP_NUMBER = INS2;

   COMMIT;

   DBMS_OUTPUT.PUT_LINE (INS); END; /

In my code, I change STATUS on MST_IP = 1 if the data already inserted in MST_ROUTER. The problem my code is not working after for insert the second row. Any comment will be appreciate.

Upvotes: 1

Views: 46

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

One trick to bring the local and remote IP addresses in line with each other is to join a subquery of local addresses to their remote counterparts, and then insert this result into the MST_ROUTER table.

INSERT INTO MST_ROUTER (USER_ID, IP_NUMBER, REMOTE_NUMBER)
SELECT
    CAST(t1.SEQ / 2 AS UNSIGNED) AS SEQ, t1.IP_NUMBER, t2.IP_NUMBER
FROM
(
    SELECT SEQ - 1 AS SEQ, IP_NUMBER
    FROM MST_IP
    WHERE MOD((SEQ - 1), 2) = 0
) t1
LEFT JOIN MST_IP t2
    ON t1.SEQ = t2.SEQ - 2

Below is a link to a demo which shows the output of the select component of my query.

Demo

Output:

SEQ IP_NUMBER   IP_NUMBER
0   12.123.0.0  12.123.0.1
1   12.104.0.1  12.104.0.2
2   13.123.0.1  13.123.0.2
3   11.555.8.1  11.555.8.2

Upvotes: 1

Related Questions