Home » RDBMS Server » Server Utilities » update values in control file
| update values in control file [message #378327] |
Tue, 30 December 2008 00:47  |
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 #378337 is a reply to message #378328] |
Tue, 30 December 2008 01:25  |
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 #378363 is a reply to message #378337] |
Tue, 30 December 2008 02:53 |
 |
Littlefoot
Messages: 21823
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
|
|
|
|
Goto Forum:
Current Time: Mon Dec 23 20:28:00 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
