Geek Answers Handbook

How to insert INTO from one mysql table into another table and set the value of one column?

I need to insert data from table1 to table2. However, I would like to set the myYear column in table2 until 2010. But in table 1 there is no column myYear.

So my main insert looks like this:

INSERT INTO `table2` ( place, event ) SELECT place, event FROM table1

Roughly, I would like to do something like the following:

 INSERT INTO `table2` ( place, event, SET myYear='2010' ) ...

Is there a way to set the column value in an insert statement?

+6
mysql insert
source share
2 answers

The following should do it:

 INSERT INTO `table2` (place, event, myYear) SELECT place, event, '2010' FROM table1;

Basic test case:

 CREATE TABLE t1 (a int, b int); CREATE TABLE t2 (c int); INSERT INTO t2 VALUES (1),(2),(3),(4),(5); INSERT INTO t1 SELECT c, 100 FROM t2; SELECT * FROM t1; +------+------+ | a | b | +------+------+ | 1 | 100 | | 2 | 100 | | 3 | 100 | | 4 | 100 | | 5 | 100 | +------+------+ 5 rows in set (0.00 sec)
+8
source share
 INSERT INTO `table2` (place, event, myYear) SELECT place, event, 2010 FROM table1

Edit: bah, did not receive a response to the message: P

+3
source share

More articles:


All Articles