OraFAQ Forum: SQL & PL/SQL » INSERT WHEN NOT EXIST

Home » SQL & PL/SQL » SQL & PL/SQL » INSERT WHEN NOT EXIST (12c)

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

INSERT WHEN NOT EXIST [message #681591]

Thu, 06 August 2020 12:06

deepakdot
Messages: 77
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

Report message to a moderator

Re: INSERT WHEN NOT EXIST [message #681592 is a reply to message #681591]

Thu, 06 August 2020 12:13

Michel Cadot
Messages: 68418
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

deepakdot
Messages: 77
Registered: July 2015

Member

I am using Oracle version 12.2.0.1.0

Report message to a moderator

Re: INSERT WHEN NOT EXIST [message #681594 is a reply to message #681593]

Thu, 06 August 2020 13:03

EdStevens
Messages: 1375
Registered: September 2013

Senior Member

Looks like you need the MERGE statement.

Report message to a moderator

Re: INSERT WHEN NOT EXIST [message #681595 is a reply to message #681594]

Thu, 06 August 2020 13:29

deepakdot
Messages: 77
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 .. "

Report message to a moderator

Re: INSERT WHEN NOT EXIST [message #681596 is a reply to message #681591]

Thu, 06 August 2020 13:35

Solomon Yakobson
Messages: 3212
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.

Report message to a moderator

Re: INSERT WHEN NOT EXIST [message #681599 is a reply to message #681596]

Fri, 07 August 2020 03:23

deepakdot
Messages: 77
Registered: July 2015

Member

This works. Thank you.

Report message to a moderator

Previous Topic:	- PDML disabled because single fragment or non partitioned table used
Next Topic:	Errors running Proc that compiles fine

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Mar 28 11:07:56 CDT 2023