Update a column in table using SQL*Loader?

问题:

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.
));

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.

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.

回答1:

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 )

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.