How to remove portal accounts that are no longer linked to a student record. 


Step 1: Back up the affected tables

example:

SELECT * INTO PWA_BACKUP_YYYYMMDD FROM PWA
SELECT * INTO PWS_BACKUP_YYYYMMDD FROM PWS


Step 2: Remove PWS records not tied to a student record

  • Identify records to remove

example:

SELECT * FROM PWS
LEFT JOIN (SELECT * FROM STU WHERE DEL = 0) STU ON PWS.ID = STU.ID
WHERE STU.ID IS NULL
  • Delete the records

example:

UPDATE PWS SET DEL = 1
FROM PWS
LEFT JOIN (SELECT * FROM STU WHERE DEL = 0) STU ON PWS.ID = STU.ID
WHERE STU.ID IS NULL

OPTIONAL: remove PWS records for students at the inactive school 

example:

UPDATE PWS SET DEL = 1
FROM PWS
INNER JOIN (SELECT * FROM STU WHERE DEL = 0) STU ON PWS.ID = STU.ID
WHERE STU.SC = [inactive school code here]


Step 3: Remove the PWA records not tied to a student in PWS

  • Identify records to remove

example:

SELECT * FROM PWA
LEFT JOIN (SELECT * FROM PWS WHERE DEL = 0) PWS ON PWS.AID = PWA.AID
WHERE PWS.AID IS NULL
  • Delete the records

example:

UPDATE PWA SET DEL = 1
FROM PWA
LEFT JOIN (SELECT * FROM PWS WHERE DEL = 0) PWS ON PWS.AID = PWA.AID
WHERE PWS.AID IS NULL