delete-bad-project-assignments.sql

DROP PROCEDURE IF EXISTS deleteBadProjectAssignments;
DELIMITER $$
CREATE PROCEDURE deleteBadProjectAssignments( )
BEGIN
	DROP TEMPORARY TABLE IF EXISTS table2;
	CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS(
	SELECT DISTINCT
		utp.project_id
	FROM
		users_to_projects utp
	INNER JOIN users u ON utp.user_id = u.user_id
	INNER JOIN projects p ON utp.project_id = p.project_id
	WHERE
		utp.project_id NOT IN(
			SELECT
				utp2.project_id
			FROM
				users_to_projects utp2
			INNER JOIN users u ON utp2.user_id = u.user_id
			WHERE
				u.user_role_id != 2
		)
	AND p.system != 1
	GROUP BY
		project_id ,
		u.user_role_id
	ORDER BY
		utp.project_id
	);


	delete from users_to_projects where project_id in (
		select project_id from table2
	);

END $$
DELIMITER ;

CALL deleteBadProjectAssignments();
DROP PROCEDURE IF EXISTS deleteBadProjectAssignments;

👍 but I'd test it on a test db first