SQL Generator To Delete EMPLIDs

By Chris Malek | Fri, Feb 9, 2024

This SQL can be useful when you need to delete EMPLIDs from all PeopleSoft tables. It generates a series of delete statements for every PeopleSoft table that contains the EMPLID field. This is in the Oracle SQL dialect. You could use this for any field or even turn it into an update statement.

Enjoy!

 SELECT 
 
 'DELETE ' || 
  CASE WHEN D.SQLTABLENAME = ' ' THEN 'PS_' || D.RECNAME ELSE D.SQLTABLENAME END 
 || ' WHERE EMPLID = ''123'';'
  FROM  
   PSRECFIELDDB C 
  , PSRECDEFN D 
  
 WHERE 
    C.RECNAME = D.RECNAME 
 
   AND C.FIELDNAME = 'EMPLID'
   AND D.RECTYPE = 0 
  
   AND EXISTS ( 
 SELECT 'X' 
  FROM all_tables 
 WHERE OWNER = 'SYSADM' 
   AND NUM_ROWS > 0 
   AND (TABLE_NAME = 'PS_' || D.RECNAME 
    OR TABLE_NAME = D.SQLTABLENAME))
    

That would generate series of delete statements like this:


DELETE PS_ACAD_HISTORY WHERE EMPLID = '123';
DELETE PS_ADM_APP_CAR_SEQ WHERE EMPLID = '123';
DELETE PS_ACAD_STDNG_ACTN WHERE EMPLID = '123';
DELETE PS_ACAD_DEGR_HONS WHERE EMPLID = '123';
DELETE PS_ACAD_DEGR_PLAN WHERE EMPLID = '123';
--- more...
Article Categories
Author Info
Chris Malek

Chris Malek is a PeopleTools® Technical Consultant with two decades of experience working on PeopleSoft enterprise software projects. He is available for consulting engagements.

About Chris Work with Chris
Product
PeopleSoft Simple Web Services (SWS)
Do you need a quick way to create web services?

Introducing a small but powerful PeopleSoft bolt-on that makes web services very easy. If you have a SQL statement, you can turn that into a web service in PeopleSoft in a few minutes.

Book
Integration Broker - The Missing Manual

I am in the process of writing a book called "Integration Broker - The Missing Manual" that you can read online.