OraFAQ Forum: SQL & PL/SQL » SQL Update - Avoid Unique Constraint Error

Home » SQL & PL/SQL » SQL & PL/SQL » SQL Update - Avoid Unique Constraint Error

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

SQL Update - Avoid Unique Constraint Error [message #621566]

Thu, 14 August 2014 07:26

gaganmadhu
Messages: 11
Registered: April 2008

Junior Member

Hello All,

Please help us to write a query to update only specific rows from the table. Requirement is to update old Employee ID with the new Employee ID, but getting "Unique Constraint" Error as certain tables already contain old and New employee IDs. We should ignore those rows and update remaining.

Create a mapping table with old and new employee ID's

SQL> CREATE TABLE PS_EMP_MAPP_TBL (OLD_EMPLID VARCHAR2(11) NOT NULL,
  2     NEW_EMPLID VARCHAR2(11) NOT NULL );

Table created

Insert rows into Mapping table.

SQL> INSERT INTO PS_EMP_MAPP_TBL  VALUES ('O12345', 'N12345');

1 row created.

SQL> INSERT INTO PS_EMP_MAPP_TBL  VALUES ('O56785', 'N56785');

1 row created.

SQL> INSERT INTO PS_EMP_MAPP_TBL  VALUES ('O67321', 'N67321');

1 row created.

SQL>

Create transaction table. One Employee has both old and New Employee IDs in this table and other two employees have only OLD employee IDs. EMPLID is key field

SQL> CREATE TABLE PS_TEST56_TBL (EMPLID VARCHAR2(11) NOT NULL PRIMARY KEY,
  EMP_NAME VARCHAR2(30) NOT NULL  
   ); 

Table Created

Insert rows into the transaction table.

SQL> INSERT INTO SYSADM.PS_TEST56_TBL VALUES ('N12345','TEST1');

1 row created.

SQL> INSERT INTO SYSADM.PS_TEST56_TBL VALUES ('O12345','TEST1');

1 row created.

SQL> INSERT INTO SYSADM.PS_TEST56_TBL VALUES ('O56785','TEST2');

1 row created.

SQL> INSERT INTO SYSADM.PS_TEST56_TBL VALUES ('O67321','TEST3');

1 row created.

Requirement is to update only two rows from Transaction table, without getting Unique constaint error for New employee ID "N12345" (for old Employee ID "O12345").

Thank You !

Report message to a moderator

Re: SQL Update - Avoid Unique Constraint Error [message #621568 is a reply to message #621566]

Thu, 14 August 2014 07:41

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Here's one option:

UPDATE ps_test56_tbl p
   SET p.emplid =
          (SELECT m.new_emplid
             FROM PS_EMP_MAPP_TBL m
            WHERE m.old_emplid = p.emplid)
 WHERE p.emplid IN (SELECT m1.old_emplid
                      FROM PS_EMP_MAPP_TBL m1
                     WHERE m1.new_emplid NOT IN (SELECT p1.emplid
                                                   FROM ps_test56_tbl p1));

Report message to a moderator

Re: SQL Update - Avoid Unique Constraint Error [message #621569 is a reply to message #621566]

Thu, 14 August 2014 07:45

gaganmadhu
Messages: 11
Registered: April 2008

Junior Member

Tables will look like this

SQL> select * from PS_EMP_MAPP_TBL ;

OLD_EMPLID  NEW_EMPLID
----------- -----------
O12345      N12345
O56785      N56785
O67321      N67321

3 rows selected.

SQL> select * from PS_TEST56_TBL;

EMPLID      EMP_NAME
----------- ------------------------------
N12345      TEST1
O12345      TEST1
O56785      TEST2
O67321      TEST3

4 rows selected.

SQL>

Report message to a moderator

Re: SQL Update - Avoid Unique Constraint Error [message #621570 is a reply to message #621569]

Thu, 14 August 2014 07:50

gaganmadhu
Messages: 11
Registered: April 2008

Junior Member

Thank you Littlefoot ! It worked. Smile

Let me appy to all the tables.

Report message to a moderator

Previous Topic:	Index and Partitions
Next Topic:	How to Send E-Mail in Oracle(PL-SQL) very urgent please

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 08:35:01 CDT 2024