I need to insert data from table1 into table2. However, I would like to set the myYear column in table2 to 2010. But, there isn't a myYear Column in table1.
我需要将table1中的数据插入table2。但是,我想将table2中的myYear列设置为2010.但是,table1中没有myYear列。
So, my basic insert looks like:
所以,我的基本插入看起来像:
INSERT INTO `table2` ( place, event )
SELECT place, event
FROM table1
Roughly, I'd 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 the insert statement?
有没有办法在insert语句中设置列值?
2 个解决方案
#1
8
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)
#2
3
INSERT INTO `table2` (place, event, myYear)
SELECT place, event, 2010
FROM table1
Edit: bah, didn't get the answer posted notification :P
编辑:呸,没有得到答案张贴通知:P
#1
8
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)
#2
3
INSERT INTO `table2` (place, event, myYear)
SELECT place, event, 2010
FROM table1
Edit: bah, didn't get the answer posted notification :P
编辑:呸,没有得到答案张贴通知:P