Home » SQL & PL/SQL » SQL & PL/SQL » INSERT WHEN NOT EXIST (12c)
INSERT WHEN NOT EXIST [message #681591] Thu, 06 August 2020 12:06 Go to next message
deepakdot
Messages: 51
Registered: July 2015
Member
Hello, I am trying to write a SQL. May be simple , but not getting a Right login. Here I am describing with a example.

One Employee may have multiple Dept no. DEPT will be an input ( lets say 20 )

CREATE TABLE T1 ( EMP VARCHAR(10), DEPT NUMBER (10));

EMP DEPT
---------
E1 10
E1 20
E2 10
E3 20

I want to insert to the same table T1, where any employee does not belongs to DEPT 20. Here only E2 does not belongs to 20. So it should insert one row with (E2,20);

INSERT INTO T1 SELECT * FROM T1 WHERE ... ?


Thanks
Deepak
Re: INSERT WHEN NOT EXIST [message #681592 is a reply to message #681591] Thu, 06 August 2020 12:13 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

INSERT statements for your test case?
Version with 4 decimals?

[Updated on: Thu, 06 August 2020 12:14]

Report message to a moderator

Re: INSERT WHEN NOT EXIST [message #681593 is a reply to message #681592] Thu, 06 August 2020 12:23 Go to previous messageGo to next message
deepakdot
Messages: 51
Registered: July 2015
Member
I am using Oracle version 12.2.0.1.0
Re: INSERT WHEN NOT EXIST [message #681594 is a reply to message #681593] Thu, 06 August 2020 13:03 Go to previous messageGo to next message
EdStevens
Messages: 1248
Registered: September 2013
Senior Member
Looks like you need the MERGE statement.
Re: INSERT WHEN NOT EXIST [message #681595 is a reply to message #681594] Thu, 06 August 2020 13:29 Go to previous messageGo to next message
deepakdot
Messages: 51
Registered: July 2015
Member
Looks like MERGE Will work for one missing Row. If I have five Employees does not belongs to dept 20, then i want to insert five rows (for each employee with dept 20).

MERGE use the syntax "INSERT (EMP,DEPT) VALUES (A.EMP, 20) " . But I think I need "INSERT INTO (EMP,DEPT) SELECT EMP, 20 from .. "
Re: INSERT WHEN NOT EXIST [message #681596 is a reply to message #681591] Thu, 06 August 2020 13:35 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2985
Registered: January 2010
Location: Connecticut, USA
Senior Member
INSERT
 INTO T1
 SELECT  DISTINCT EMP,
                  20
   FROM  T1 A
   WHERE NOT EXISTS(
                    SELECT  1
                      FROM  T1 B
                      WHERE B.EMP = A.EMP
                        AND B.DEPT = 20
                   )
/
SY.
Re: INSERT WHEN NOT EXIST [message #681599 is a reply to message #681596] Fri, 07 August 2020 03:23 Go to previous message
deepakdot
Messages: 51
Registered: July 2015
Member
This works. Thank you.
Previous Topic: - PDML disabled because single fragment or non partitioned table used
Next Topic: Errors running Proc that compiles fine
Goto Forum:
  


Current Time: Mon Sep 21 00:41:21 CDT 2020