I was tasked to create a database for a gym system for my project, I need help on what MySQL query should I use for my program.

First query: Remove rows in the TBL_ACTIVEMEMBER where the END column date is past the current date.

Second query: Transfer and insert those removed rows into another table which is TBL_INACTIVEMEMBER.

So these are my tables:

1. TBL_ACTIVEMEMBER:

 ------ --------- -------- ---------- ---------- ------------ ------------ 
| AMID | GYMID   | INSTID | TYPE     | TERM     | START      | END        |
 ------ --------- -------- ---------- ---------- ------------ ------------ 
|    1 | 2021001 |   4502 | Gold     | 3 Months | 2021-12-18 | 2022-03-18 |
|    2 | 2021003 |   4504 | Platinum | 3 Months | 2021-12-13 | 2022-03-13 |
|    3 | 2021002 |   4502 | Silver   | 3 Months | 2022-01-15 | 2022-04-15 |
|    4 | 2021004 |   4502 | Platinum | 5 Months | 2021-12-25 | 2022-05-25 |
|    5 | 2021006 |   4503 | Silver   | 5 Months | 2021-12-18 | 2022-05-18 |
|    6 | 2021007 |   4502 | Silver   | 1 Month  | 2021-12-24 | 2022-01-24 |
|    7 | 2021008 |   4501 | Gold     | 3 Months | 2021-12-18 | 2022-03-18 |
|    8 | 2021008 |   4503 | Platinum | 1 Month  | 2021-12-12 | 2022-01-12 |
|    9 | 2021009 |   4502 | Silver   | 3 Months | 2022-01-08 | 2022-04-08 |
 ------ --------- -------- ---------- ---------- ------------ ------------ 

2. TBL_INACTIVEMEMBER:

 ------- --------- ------ ----- --------- ---------------- 
| Field | Type    | Null | Key | Default | Extra          |
 ------- --------- ------ ----- --------- ---------------- 
| INAMID| int(11) | NO   | PRI | NULL    | auto_increment |
| GYMID | int(11) | YES  |     | NULL    |                |
| END   | date    | NO   |     | NULL    |                |
 ------- --------- ------ ----- --------- ---------------- 

CodePudding user response：

INSERT INTO TBL_INACTIVEMEMBER (Column1, Column2, ColumnN) 
SELECT * FROM TBL_ACTIVEMEMBER where END < CURRENT_TIMESTAMP();

This will insert the required values into the table TBL_INACTIVEMEMBER. Change the column names as required.

Now to delete the inactive values from table TBL_ACTIVEMEMBER,

DELETE FROM TBL_ACTIVEMEMBER WHERE END < CURRENT_TIMESTAMP()