在子查询后删除并返回结果

Question

Right now I have the following SQL statement that I'm calling in PHP:

SELECT * FROM activity WHERE id=(
    SELECT activity_id FROM session_notifications
    WHERE session_id='{$session}');

This pulls table data from activities that are referenced in the session_notification table. All good. Now, is there an easy way to do this, but also in the same statement delete all of the rows returned by checking session_notifications?

** Edit: To be more clear, I do not want to delete the activity since I'll need that information. I want to delete the rows in session_notifications that were hit. Why? Because I don't want anything to have been added in afterwards. However, I might be okay since SQL tables are thread safe, yes?

Answer 1

If you want to make sure you will delete the records shown in the select, you must use a transaction.

START TRANSACTION;
SELECT * FROM activity WHERE id IN (
    SELECT activity_id FROM session_notifications
    WHERE session_id='{$session}');
DELETE FROM activity WHERE id IN (
    SELECT activity_id FROM session_notifications
    WHERE session_id='{$session}');
COMMIT;