使用 SQL*Loader 更新表中的列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9091167/
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
Update a column in table using SQL*Loader?
提问by vikiiii
I have written a SQL Script having below query. Query works fine.
我编写了一个具有以下查询的 SQL 脚本。查询工作正常。
update partner set is_seller_buyer=1 where id in (select id from partner
where names in
(
'A','B','C','D','E',... // around 100 names.
));
But now instead of writing around 100 names in a query itself , I want to fetch all the names from the CSV file. I read about SQL*Loader on internet but i did not get much on update query. My csv file contain only names.
但是现在我不想在查询本身中写入大约 100 个名称，而是想从 CSV 文件中获取所有名称。我在 Internet 上阅读了 SQL*Loader，但我对更新查询的了解不多。我的 csv 文件只包含名称。
I have tried
我试过了
load data
infile 'c:\data\mydata.csv'
into table partner set is_wholesaler_reseller=1
where id in (select id from partner
where names in
(
'A','B','C','D','E',... // around 100 names.
));
fields terminated by "," optionally enclosed by '"'
( names, sal, deptno )
How i can achieve this? Thanks in advance.
我怎么能做到这一点？提前致谢。
回答by John Doyle
SQL*Loader does not perform updates, only inserts. So, you should insert your names into a separate table, say names, and run your update from that:
SQL*Loader 不执行更新，只执行插入。因此，您应该将您的姓名插入到一个单独的表中，例如names，并从中运行您的更新：
update partner set is_seller_buyer=1 where id in (select id from partner
where names in
(
select names from names
));
Your loader script can be changed to:
您的加载程序脚本可以更改为：
load data
infile 'c:\data\mydata.csv'
into table names
fields terminated by "," optionally enclosed by '"'
( names, sal, deptno )
An alternate to this is to use External Tables which allows Oracle to treat a flat file like it is a table. An example to get you started can be found here.
另一种方法是使用外部表，它允许 Oracle 将平面文件视为表。可以在此处找到帮助您入门的示例。
