How to insert values into table based on existing rows in table

Question

I have a table inv_dtl with a sequence as primary key. I need an insert statement to insert values as per the following requirements:

If the row already exists in the table(excluding primary key value), I need to insert the values with the last column(status_flag) with value 'I'.
If it doesn't exist, I need to insert the values with status_flag column value 'A'.

What did you mean under exists in the table(excluding primary key value)? — oreh
– oreh, Commented May 7, 2018 at 9:39
@maxim - I mean if the row I am inserting is already present in the table. The primary key value won't be already present as I am using a sequence for it. — Mano
– Mano, Commented May 7, 2018 at 9:44
so row is present if there is a row in the table with all columns equal to values you are going to insert. right? and in this case you leave one row with status A and insert new one with status I? — oreh
– oreh, Commented May 7, 2018 at 9:46
yes. if it is present, I have to insert a column value as 'I' and if not present I have to insert it as 'A'. All the other column values will be inserted as it is. — Mano
– Mano, Commented May 7, 2018 at 9:49
Posted another answer with step by step, check out that will be useful to you. — user8406805
– user8406805, Commented May 7, 2018 at 11:20

oreh · Accepted Answer · 2018-05-07 11:36:55Z

3

As I understand you are using some language to work with a database.

There are two ways:

First check if a record is already exist: select 1 from inv_dtl where col1 = val1 and col2 = val2 etc. Then insert new record with different flags. Which flag you are deciding in source code of your language.
Use tricky sql statement: insert into inv_dtl(col1, col2, etc.) values(val1, val2, etc., case (select 'X' from inv_dtl where col1 = val1 and col2 = val2, etc.) when 'X' then 'I' else 'A' end)

edited May 7, 2018 at 11:36

answered May 7, 2018 at 10:08

oreh

1,2011 gold badge10 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

San · Accepted Answer · 2018-05-07 11:04:30Z

From the question, it seems like at there can be only one row for all matching records for which status_flag will be 'A', assuming this, you can write a query using left outer join on the new values and then use a case statement to identify the value of status_code.

INSERT INTO inv_dtl
SELECT a.p_id,
       a.col1,
       a.col2,
       CASE WHEN b.status_flag IS NULL THEN 'A' ELSE 'I' END AS status_flag
  FROM (SELECT 1 p_id, -- new values goes here
               100 col1,
               'new 2' col2,
               'A' status_flag
          FROM dual) a
  LEFT OUTER JOIN (SELECT * FROM inv_dtl) b
    ON b.col1 = a.col1 -- all columns list goes here
   AND b.col2 = a.col2
   AND b.status_flag = 'A'; -- Status 'A' is fixed.

Testing the code:

SQL> create table inv_dtl(p_key number, col1 number, col2 varchar2(10), status_flag varchar2(1));
Table created

SQL> INSERT INTO inv_dtl
  2  SELECT a.p_id,
  3         a.col1,
  4         a.col2,
  5         CASE WHEN b.status_flag IS NULL THEN 'A' ELSE 'I' END AS status_flag
  6    FROM (SELECT 1 p_id,
  7                 100 col1,
  8                 'new' col2,
  9                 'A' status_flag
 10            FROM dual) a
 11    LEFT OUTER JOIN (SELECT * FROM inv_dtl) b
 12      ON b.col1 = a.col1
 13     AND b.col2 = a.col2
 14     AND b.status_flag = 'A';
1 row inserted

SQL> 
SQL> INSERT INTO inv_dtl
  2  SELECT a.p_id,
  3         a.col1,
  4         a.col2,
  5         CASE WHEN b.status_flag IS NULL THEN 'A' ELSE 'I' END AS status_flag
  6    FROM (SELECT 2 p_id,
  7                 100 col1,
  8                 'new' col2,
  9                 'A' status_flag
 10            FROM dual) a
 11    LEFT OUTER JOIN (SELECT * FROM inv_dtl) b
 12      ON b.col1 = a.col1
 13     AND b.col2 = a.col2
 14     AND b.status_flag = 'A';
1 row inserted

SQL> 
SQL> INSERT INTO inv_dtl
  2  SELECT a.p_id,
  3         a.col1,
  4         a.col2,
  5         CASE WHEN b.status_flag IS NULL THEN 'A' ELSE 'I' END AS status_flag
  6    FROM (SELECT 3 p_id,
  7                 100 col1,
  8                 'new' col2,
  9                 'A' status_flag
 10            FROM dual) a
 11    LEFT OUTER JOIN (SELECT * FROM inv_dtl) b
 12      ON b.col1 = a.col1
 13     AND b.col2 = a.col2
 14     AND b.status_flag = 'A';
1 row inserted

SQL> 
SQL> INSERT INTO inv_dtl
  2  SELECT a.p_id,
  3         a.col1,
  4         a.col2,
  5         CASE WHEN b.status_flag IS NULL THEN 'A' ELSE 'I' END AS status_flag
  6    FROM (SELECT 4 p_id,
  7                 200 col1,
  8                 'new 2' col2,
  9                 'A' status_flag
 10            FROM dual) a
 11    LEFT OUTER JOIN (SELECT * FROM inv_dtl) b
 12      ON b.col1 = a.col1
 13     AND b.col2 = a.col2
 14     AND b.status_flag = 'A';
1 row inserted

SQL> 
SQL> INSERT INTO inv_dtl
  2  SELECT a.p_id,
  3         a.col1,
  4         a.col2,
  5         CASE WHEN b.status_flag IS NULL THEN 'A' ELSE 'I' END AS status_flag
  6    FROM (SELECT 5 p_id,
  7                 200 col1,
  8                 'new 2' col2,
  9                 'A' status_flag
 10            FROM dual) a
 11    LEFT OUTER JOIN (SELECT * FROM inv_dtl) b
 12      ON b.col1 = a.col1
 13     AND b.col2 = a.col2
 14     AND b.status_flag = 'A';
1 row inserted

SQL> select * from inv_dtl;
     P_KEY       COL1 COL2       STATUS_FLAG
---------- ---------- ---------- -----------
         1        100 new        A
         2        100 new        I
         3        100 new        I
         4        200 new 2      A
         5        200 new 2      I

SQL>

think it is possible to replace LEFT OUTER JOIN (SELECT * FROM inv_dtl) b with LEFT OUTER JOIN inv_dtl b
This was to provide flexibility if you want to filter more records from inv_dtl table and 'A' status flag is used and can be replaced with any other value if user wants the status flag as an input, just modify the case statement. User can remove them if not required.

score 1 · Accepted Answer · 2018-05-07 10:01:02Z

1

Assuming table "inv_dtl" contains fields ("ID","status_flag"). And an sequence named "inv_dtl_seq".

First, you have to create a function as below

CREATE OR REPLACE FUNCTION inv_dtl_seq_fnc
 RETURN NUMBER
IS
  v_seq_val  NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'select inv_dtl_seq.nextval from dual'
     INTO v_seq_val;

  RETURN v_seq_val;
END inv_dtl_seq_fnc;

Now, try the below SQL block, will be useful to you.

merge into inv_dtl i
using (select inv_dtl_seq_fnc new_id from dual) d
  on (i.id = d.new_id)
when matched then 
  update set status = 'I'
when not matched then
insert values(d.new_id,'A');

edited May 7, 2018 at 10:01

answered May 7, 2018 at 9:37

user8406805

5 Comments

Mano Over a year ago

Thank you. But I cannot use pl/sql here as the insert happens once a button is clicked from front end.

user8406805 Over a year ago

Check out my updated answer. here you have to create a function first. Then you can use the merge statement which compares and then update/insert based on the matching of ID.

oreh Over a year ago

As I understood new record must be inserted in both cases.

user8406805 Over a year ago

Yes, if the NEW ID already exists then it will update the status as "I" else it will insert the new record.

Mano Over a year ago

Yes, maxim got it right. I need to insert in both cases. no update at all.

user8406805 · Accepted Answer · 2018-05-07 11:15:56Z

Here is the step by step, to your solution. check out might be useful to you.

Creating a FUNCTION:

CREATE OR REPLACE FUNCTION inv_dtl_seq_fnc(p1 number)
 RETURN NUMBER
IS
  v_seq_val  NUMBER;
BEGIN
if p1 = 0 then
  EXECUTE IMMEDIATE 'select inv_dtl_seq.nextval from dual'
     INTO v_seq_val;
elsif p1=1 then
  EXECUTE IMMEDIATE 'select inv_dtl_seq.currval from dual'
     INTO v_seq_val;
end if;

  RETURN v_seq_val;
END inv_dtl_seq_fnc;

Actual use case:

create table inv_dtl(id number, status varchar2(200));
create sequence inv_dtl_seq
     start with 1
     increment by 1 
     nocache 
     nocycle;


insert into inv_dtl values(1,'A');
select * from inv_dtl;

ID  STATUS
1   A

insert into inv_dtl(id,status) 
  values (inv_dtl_seq_fnc(0),case when (select count(1) from inv_dtl where id = inv_dtl_seq_fnc(1)) > 0 then 'I' else 'A' end);

select * from inv_dtl;
ID  STATUS
1   A
1   I

insert into inv_dtl(id,status) 
  values (inv_dtl_seq_fnc(0),case when (select count(1) from inv_dtl where id = inv_dtl_seq_fnc(1)) > 0 then 'I' else 'A' end);

ID  STATUS
1   A
1   I
2   A

Collectives™ on Stack Overflow

How to insert values into table based on existing rows in table

4 Answers 4

Comments

3 Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related