使用SQL * Loader更新表中的列? [英] Update a column in table using SQL*Loader?
问题描述
我写了一个具有以下查询的SQL脚本. 查询工作正常.
I have written a SQL Script having below query. Query works fine.
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.
));
但是现在,我不想在查询本身中编写大约100个名称,而是要从CSV文件中获取所有名称. 我在互联网上读到有关SQL * Loader的信息,但在更新查询上却得不到很多. 我的csv文件仅包含名称.
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.
我尝试过
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.
推荐答案
SQL * Loader不执行更新,仅执行插入.因此,您应该将名称插入一个单独的表中,例如names
,然后从该表运行更新:
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:
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 )
对此的一种替代方法是使用外部表,该表允许Oracle将平面文件当作表一样对待.可以在此处找到示例.
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.
这篇关于使用SQL * Loader更新表中的列?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!