Can a MySQL event delete multiple rows instantly?

Well i am doing my diploma thesis and i had an idea in mind for the database of the program i am designing. I am doing a Gym Membership Manager and i the main purpose is to keep records of the clients memberships, my tables are as follows:

|Client Info|

-ID

+Name

|Client Membership|

Client ID

Membership ID

-Starting Date

-Ending Date

|Expired Memberships|

+Client Name

+Ending Date

The event i have defined is as follows:

BEGIN
    INSERT INTO `expired_memberships`
SET
    `Client Name` =(
    SELECT
        CI.`Name`
    FROM
        `client_info` AS CI
    INNER JOIN `client_membership` AS CM
    ON
        CI.`client_id` = CM.`Client ID`
    WHERE
        CM.`Ending Date` < CURRENT_DATE()
    ),
        `Ending Date` = CURRENT_DATE();

    DELETE
FROM
    `client_membership`
WHERE
    `Ending Date` < CURRENT_DATE();
    END

I searched around the internet on how to check if a event works because i was doubting that it would work and i found a thread here in stackoverflow saying to copy paste the event in a stored procedure and run it but when i tried it said subquery has more than one result because there were two rows to delete on the client_membership table, i thought if i should have added like for each row but i’m not sure. The main idea of the event is to transfer records from client_memberships to expired_memberships. HELP PLS!

Answer

Change your insert to a form that you can save as amny as you need

INSERT INTO `expired_memberships`
SELECT
    CI.`Name`,CURRENT_DATE()
FROM
    `client_info` AS CI
INNER JOIN `client_membership` AS CM
ON
    CI.`client_id` = CM.`Client ID`
WHERE
    CM.`Ending Date` < CURRENT_DATE();

And of course you can delete multiple rows, if need be.

Advertisement

Answer