Inserting some columns from one table to another and including current date as well MYSQL

Question

I have a MySQL database with two tables as displayed here:

Balance Table

Id: int [PK] AutoIncrementing
AccountId: Varchar(100) 
UserBalance: Double
DateEntered: Date

Account Table (private info censored)

AccountId: Varchar(100) [PK] Not AutoIncrementing
Email: Varchar(45)
Balance: Double
Role: Varchar(5)
Username: Varchar(45)
HasInvested: Tinyint(1)

What I want to do is take the AccountIds and Balances of all rows from the Account Table and insert them into the equivalent columns in the Balance Table which I have already done with the following code:

use database;
insert into balance (AccountID, UserBalance)select AccountId, Balance from account

however I also want the code to automatically fill in the DateEntered columns of each row in the Balance table with the current date using the CURDATE() Function when executed. How should I alter my code to do this?

Answer 1

however I also want the code to automatically fill in the DateEntered columns of each row in the Balance table with the current date using the CURDATE() Function when executed. How should I alter my code to do this?

You don't have to do this. You must simply set DEFAULT value for this column:

ALTER TABLE Balance
    ALTER COLUMN DateEntered DEFAULT CURRENT_TIMESTAMP;

Now, if this column is not mentioned in INSERT query or the keyword DEFAULT is used as a value for this column, the column will be set with current datatime value automatically.

Automatic Initialization and Updating for TIMESTAMP and DATETIME.

There is a little problem - this cannot be applied to DATE column, it ia applicable only to DATETIME and TIMESTAMP. But I think that simplification may be more important that small row size increasing.

---

If you need in the column whose datatype is DATE strictly then you may add autoutilized column and make DateEntered column generated:

ALTER TABLE Balance
    ADD COLUMN created_at DATETIME DEFAULT CURRENT_TIMESTAMP;
UPDATE Balance SET created_at = DateEntered;
ALTER TABLE Balance
    DROP COLUMN DateEntered,
    ADD COLUMN DateEntered DATE AS (DATE(created_at)) VIRTUAL;

But in this case you cannot edit the value of this column (edit created_at column, and the value of DateEntered will be changed automatically) or insert into this column (you cannot mention it in the columns list in INSERT query).

---

If you need this column to be DATE strictly then I recommend to create a trigger which sets the value for this column if it is not specified:

CREATE TRIGGER tr_bi_balance
BEFORE INSERT ON Balance
FOR EACH ROW 
SET NEW.DateEntered = COALESCE(NEW.DateEntered, CURRENT_DATE);

The value will be set to current date only if the value is not specified in the query explicitly or if NULL is specified.