将一个表中的一列数据插入到另一个表中,但另一列数据将被动态指定 [英] insert one column data from a table into other table, but the other column data will be specified dynamically
问题描述
我有两张桌子.表 A 列 - 帮助,姓名表 B 列 - bid , bname
i have 2 tables. TABLE A COLUMNS - aid , aname TABLE B COLUMNS - bid , bname
从表A中,我将从'aid'列中提取数据,并将其插入表 B 的 'bid' 列中,但在表 B 的 bname 列中,我将插入一个新值.我该怎么做?
from table A, i will pick up data from column 'aid', AND insert it in 'bid' column of table B , but in bname column of table B, i will insert a new value. how do i do this?
create table A(aid int,aname char)
insert into A values(111, 'e')
create table B(bid int, bname char)
insert into B (bid,bname)
bid 将从查询中获取价值:从 abname 会得到一个新值 -m
bid will take value from the query : select aid from a bname will get a new value -m
预期结果应该是:表 B 将有:
expected result should be : THE TABLE B WILL HAVE :
出价名称---- -----111米
bid bname --- ----- 111 m
推荐答案
试试这个:
insert into b (bid, bname) select aid, 'm' as bname_fixed_val from a
两个事实支持上述解决方案:
Two facts enabled the solution above:
insert .. select
子句允许您插入任何select
返回的值.您可以使用
select
将常量值作为字段返回,例如:
- The
insert .. select
clause allows you to insert the values returned with anyselect
. You can return constant values as fields with
select
, like for instance:
SELECT 0 as id, 'John' as name
将这两点结合在一起,我使用 insert..select
子句从第一个表 (aid
) 中选择字段值,以及一个常量值第二个字段 (m
).AS bname_fixed_val
子句只是一个字段别名,可以省略.
Combining these two points together, I used an insert..select
clause to select the field value from the first table (aid
), along with a constant value for the second field (m
). The AS bname_fixed_val
clause is simply a field alias, and can be omitted.
有关 SQL 的更多信息,请访问以下链接:http://www8.silversand.net/techdoc/teachsql/index.htm,虽然谷歌搜索也无妨.
For more information on SQL, here 's a link: http://www8.silversand.net/techdoc/teachsql/index.htm, although googling it wouldn't hurt also.
这篇关于将一个表中的一列数据插入到另一个表中,但另一列数据将被动态指定的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!