SQL 从 SELECT 插入但更改一列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15972743/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Inserting from a SELECT but changing one column?
提问by bek
Wondering if there is a way to insert a row into a table from another, with exception of one column?
想知道是否有办法从另一个表中插入一行，但一列除外？
This is of course easy with a limitied amount of columns, but gets kind of tiredsome listing all of the columns when the number of columns increases.
对于有限数量的列，这当然很容易，但是当列数增加时，列出所有列会有点累。
I'm thinking something in the line of:
我在想以下几点：
Insert into table
select * replace col1 with current date
from table
where yada yada yada
One possiblilty would be to duplicate one row and perform and update, but let's say that's not an option due to an index or something.
一种可能性是复制一行并执行和更新，但假设由于索引或其他原因，这不是一个选项。
Any ideas?
有任何想法吗？
Edit: It's DB2 v10, but the question is out of pure curiousity. Just wondering if it is possible...
编辑：这是 DB2 v10，但这个问题纯粹是出于好奇。只是想知道是否有可能...
采纳答案by Arkadiusz Nowak
using Oracle
使用甲骨文
DECLARE
CURSOR CUR_D IS
SELECT *
FROM TABLE
WHERE id = some id;
ROW_D CUR_D%ROWTYPE;
BEGIN
OPEN CUR_D;
FETCH CUR_D INTO ROW_D;
CLOSE CUR_D;
ROW_D.column := 'some data';
INSERT INTO some table
VALUES ROW_D;
END;
/
回答by Sawan Vaidya
You can use temporary tables
您可以使用临时表
Create Temporary table
创建临时表
CREATE TEMPORARY TABLE temp_table AS (SELECT * FROM MyTable WHERE ...);
Update column
更新列
UPDATE temp_table SET column='Value' WHERE ...;
Or drop a column
或删除一列
ALTER TABLE temp_table DROP column_name;
Insert to destination table
插入到目标表
INSERT INTO MyDestinationTable SELECT * FROM temp_table;
回答by Andomar
For SQL Server, the syntax would be:
对于 SQL Server，语法为：
insert into TargetTable
(col1, col2, col3, ...)
select getdate(), col2, col3, ...
from SourceTable
SQL Server can generate a column list for you. Right click the table name, and choose Script as -> Select -> To New Window.
SQL Server 可以为您生成一个列列表。右键单击表名，然后选择Script as -> Select -> To New Window。
回答by SMS
Create a VIEW with required number for columns.
创建一个具有所需列数的视图。
Assume Tbl1 has 4 columns. Create a view with required columns. Here Col1 has been excluded.
假设 Tbl1 有 4 列。创建具有所需列的视图。此处已排除 Col1。
CREATE VIEW V1 AS
SELECT col2, col3, col4
FROM TBL1
Use the VIEW for Inserting. Col1 value for TBL2 will be current date, for other columns, the values will be from view V1 ie., col2, col3, col4 from TBL1
使用 VIEW 进行插入。TBL2 的 Col1 值将是当前日期，对于其他列，值将来自视图 V1，即来自 TBL1 的 col2、col3、col4
INSERT INTO TBL2
SELECT GETDATE(), *
FROM V1
This way you dont need to specify all the columns each time.
这样你就不需要每次都指定所有的列。
Hope it helps
希望能帮助到你
回答by collapsar
assuming that your select has attributes uniquely defining the result set wrt to the contents of your target table before insertion, you can apply the following 2 steps:
假设您的选择具有在插入之前唯一地将结果集定义为目标表的内容的属性，您可以应用以下两个步骤：
Insert into target_table
select *
from source_table
where yada yada yada
and characteristic_yada
;
update target_table
set col1 = current date
where characteristic_yada
;
commit;
make sure to issue both commands inside the same transaction as shown. also be aware that characteristic_yadamust be aplicable to source and target table alike and that the suitability of characteristic_yadaneeds to be checked before each application of the statements unless they refer to pks/aks of the taregt table
确保在同一个事务中发出两个命令，如图所示。还请注意，characteristic_yada必须适用于源表和目标表，并且characteristic_yada在每次应用语句之前都需要检查其适用性，除非它们引用了目标表的 pks/aks
回答by zim_pa
zim RDBMS will allow it. All fields matching by fieldname will obtain values from the source, unless overridden by a let statement. eg.
zim RDBMS 将允许它。所有通过 fieldname 匹配的字段都将从源中获取值，除非被 let 语句覆盖。例如。
`add all details from details where account_month = 20190101 let level = 2 increase = base * 1.1 total = increase * amount`
This would for all detail records for the specified accounting month, add a level 2 record with all field values matching the source set, except these assigned/calculated amounts: level is 2. increase is original base times 1.1. total is new increase (original base times 1.1) times the amount. If you switch the order of assignment of increase and total, then total will use the source/original increase in the calculation.
这将为指定会计月份的所有明细记录添加一个级别 2 记录，其所有字段值都与源集匹配，但这些分配/计算的金额除外：级别为 2。增加是原始基数乘以 1.1。总数为新增（原基数乘以1.1）乘以金额。如果您切换增加和总计的分配顺序，则总计将在计算中使用源/原始增加。
