Home » SQL & PL/SQL » SQL & PL/SQL » SQL Update - Avoid Unique Constraint Error
SQL Update - Avoid Unique Constraint Error [message #621566] Thu, 14 August 2014 07:26 Go to next message
gaganmadhu
Messages: 8
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 !
Re: SQL Update - Avoid Unique Constraint Error [message #621568 is a reply to message #621566] Thu, 14 August 2014 07:41 Go to previous messageGo to next message
Littlefoot
Messages: 19630
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));
Re: SQL Update - Avoid Unique Constraint Error [message #621569 is a reply to message #621566] Thu, 14 August 2014 07:45 Go to previous messageGo to next message
gaganmadhu
Messages: 8
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>
Re: SQL Update - Avoid Unique Constraint Error [message #621570 is a reply to message #621569] Thu, 14 August 2014 07:50 Go to previous message
gaganmadhu
Messages: 8
Registered: April 2008
Junior Member
Thank you Littlefoot ! It worked. Smile

Let me appy to all the tables.
Previous Topic: Index and Partitions
Next Topic: How to Send E-Mail in Oracle(PL-SQL) very urgent please
Goto Forum:
  


Current Time: Sun Sep 21 23:15:58 CDT 2014

Total time taken to generate the page: 0.19278 seconds