问题描述:

I have a MySQL table with about 30 fields. I need to create a second table with a subset of those fields, plus the CURDATE() to track the history of their spending. I'm getting syntax errors, but perhaps my method is incorrect to begin with. Please review this, thanks!

INSERT INTO history (customer_id, name, amount, date_recorded)

set date_recorded = CURDATE()

SELECT customer_id, name, amount

FROM master;

What I'm attempting to do in MySQL in the above example is copy the fields of customer_id, name, amount from the master customer list, and include today's date. The record layout for the history table is here:

+---------------+------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+---------------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| customer_id | int(11) | NO | PRI | NULL | |

| name | varchar(255) | NO | | NULL | |

| amount | int(11) | NO | | NULL | |

| date_recorded | date | NO | | NULL | |

+---------------+------------------+------+-----+---------+----------------+

网友答案:

Try this::

INSERT INTO history (customer_id, name, amount, date_recorded)

SELECT customer_id, name, amount, CURDATE()
FROM master

One more way is ::

INSERT INTO history (customer_id, name, amount, date_recorded)

SELECT customer_id, name, amount, DATE(now())
FROM master
相关阅读:
Top