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!
Advertisement
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.