i have 2 tables users and userroles with common column 'externalid'
in users tables i have deleted many entries which are no longer needed but userroles has 628 rows. i want to delete all rows in userroles with 'externalid' column values which do not exist in users.
i did same thing 6 months ago but now i can't remember.
ON users.externalid = userroles.externalid
WHERE users.externalid IS NULL
you might want to investigate declaring the externalid in userroles as a foreign key to the users table, with ON DELETE CASCADE option
that way, when you delete a user, the related userroles row(s) are automatically also deleted
thank you very much.
I know foreign key is better idea but i have no authority to change the tables.
not a single table has foreign key constraint.
only thing i allowed to do is that after 6 months empty all the tables and keep some users in user tables.
next iteration is final iteration.
they are considering to create new application from scratch with new database design.