Home » RDBMS Server » Server Utilities » update values in control file
update values in control file [message #378327] Tue, 30 December 2008 00:47 Go to next message
radhavijaym
Messages: 65
Registered: December 2008
Location: singapore
Member
hi,
i had a control file in which i will upload data from data file in to temporary table.
in the table 2 columns cpf_no and uen_no.
in data file
if cpf_no is null and uen_no is not null i will some records like this
now after inserting into table (ttas_invoice) i want to update cpf_no =uen_no where cpf_no is null and uen_no is not null.

example:
data file
cpf_no uen_no nric
100
101
102 102
190

after uploading in ttas_invoice i will have values like this
cpf_no uen_no nric
100
101
102 102
190

so now i wnat to update cpf_no=101 where uen_no=101 and cpf_no is null please help me how to do this.
im attaching the file.
Re: update values in control file [message #378328 is a reply to message #378327] Tue, 30 December 2008 00:52 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Write a query which will do the update; run it after loading session is over.
Re: update values in control file [message #378337 is a reply to message #378328] Tue, 30 December 2008 01:25 Go to previous messageGo to next message
radhavijaym
Messages: 65
Registered: December 2008
Location: singapore
Member
IN CONTROL FILE WE CANT USE UPDATE COMMAND SO HOW CAN I UPDATE AFTER LOADING SESSION

load data
-- amended on 17 AUGUST 2007 BY NIIT -- SAILSUDHA:
infile 'F:\INTERFACE\TTAS\data\ttasinv.csv'
replace
into table
TTAS_INVOICE fields terminated by "," optionally enclosed by '"'
trailing nullcols
(
CPF_NO ,
UEN_NO ,
NRIC_NO ,
PAYEE_NAME ,
ADDRESS_TYPE ,
FLOOR_NO ,
STREET_NAME ,
BLOCK ,
BUILDING_NAME ,
UNIT ,
POSTAL_CODE ,
ADDRESS_LINE1 ,
ADDRESS_LINE2 ,
ADDRESS_LINE3 ,
TELEPHONE_NO ,
FAX_NO ,
TAX_INVOICE_NO ,
RECEIPT_NO ,
TAX_INVOICE_DATE DATE "DD/MM/RR",
TAX_INVOICE_TYPE ,
INDICATOR ,
PAYMENT_MODE1 ,
AMOUNT1 DECIMAL EXTERNAL,
CHEQUE_NO1 ,
BANK_CODE1 ,
PAYMENT_MODE2 ,
AMOUNT2 DECIMAL EXTERNAL,
CHEQUE_NO2 ,
BANK_CODE2 ,
REFUND_AMOUNT DECIMAL EXTERNAL,
TRANSACTION_TYPE ,
---------------------
LINE_NO DECIMAL EXTERNAL,
ITEM_CODE ,
QUANTITY ,
LINE_AMOUNT DECIMAL EXTERNAL,
COURSE_FEE DECIMAL EXTERNAL,
GST_AMOUNT DECIMAL EXTERNAL,
APPLICANT_NAME ,
APPLICANT_IC_NO ,
SDF_FLAG ,
TAX_CODE ,
INTERFACE_SEQ_NO sequence(max,1))
--BY THE ABOVE LINE THE CONTROL FILE IS COMPLETED.

--THE BELOW IS THE STATEMENT I WANT TO ADD

UPDATE TTAS_INVOICE SET CPF_NO=UEN_NO WHERE CPF_NO IS NULL AND UEN_NO IS NOT NULL



SO please HELP ME HOW TO DO THIS.




Re: update values in control file [message #378345 is a reply to message #378337] Tue, 30 December 2008 01:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't post in UPPER case.
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Have a look at external table.

Regards
Michel
Re: update values in control file [message #378363 is a reply to message #378337] Tue, 30 December 2008 02:53 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
External tables, as Michel suggested, are nice way to do the job (if your database version supports them).

However, my idea was to create an operating system batch file which would
a) call SQL*Loader and load data into a table
b) call SQL script which would update records

So, basically, you'd need three files:
  • SQL*Loader control file
  • SQL script (UPDATE table SET ...)
  • batch file
    SQLLDR scott/tiger CONTROL=load.ctl ...
    SQLPLUS -S scott/tiger @update_table.sql
Previous Topic: Transportable tablespaces
Next Topic: Loading batch
Goto Forum:
  


Current Time: Sun Dec 11 06:23:40 CST 2016

Total time taken to generate the page: 0.16175 seconds