Home » SQL & PL/SQL » SQL & PL/SQL » Update Table with Other Table (Oracle 10g)
Update Table with Other Table [message #331152] |
Wed, 02 July 2008 07:27  |
pcd_lou
Messages: 63 Registered: October 2007 Location: Florida
|
Member |
|
|
Hi Everyone....
The Idea is to Extract all the addresses from one Table and then Update these with information from a Subscription table and an E-Mail Table using the Customer Number in the Address Table to match. There will be rows in the ADR Table that do not have a match in the Sub table.
Here's the code I have:
Update T_LRExt1 Set
T_Client_Code = pub_cde, -- CDSADR_M/PUB_CDE
T_Pub_Code = ' ', -- Blank - Publication Code
T_Zip = SubStr(zip_cde, 1, 5), -- CDSADR_M/ZIP_CDE
T_Zip4 = SubStr(zip_cde, 6, 9), -- CDSADR_M/ZIP_CDE
T_Carrier_Route = ' ', -- Blank
T_Address_Type = ' ', -- Translated From CDSADR_M/STR_1ST
T_Postal_Code = SubStr(zip_cde, 1, 6), -- CDSADR_M/ZIP_CDE (Canada Only)
T_Title_Code = ' ', -- Translated From CDSADR_M/CTM_TTL
T_Name = atn_1st || atn_mid || atn_end, -- CDSADR_M/ATN_1ST, ATN_MID, ATN_END
T_Company = SubStr(cmp_nme, 1, 35), -- CDSADR_M/CMP_NME
T_Prim_Adr = SubStr(str_1st, 1, 35), -- CDSADR_M/STR_1ST
T_Secd_Adr = SubStr(str_2nd, 1, 35), -- CDSADR_M/STR_2ND
T_City = SubStr(ctm_cty, 1, 20), -- CDSADR_M/CTM_CTY
T_State = SubStr(ctm_ste, 1, 02), -- CDSADR_M/STR_STE
T_Acct_Num = LPad(ctm_nbr, 018, '0'), -- CDSADR_M/CTM_NBR
T_Bill_Eff = ' ', -- ???
T_Gender = sex_cde, -- CDSADR_M/SEX_CDE
T_BusinessCode = ' ', -- Blank
T_Country = cun_typ, -- CDSADR_M/Cun_Typ
T_Delivery_Geo_I = ' ', -- Blank - Traffic Code????
T_Match_Phone = phn_nbr, -- CDSADR_M/Phn_Nbr
T_Multi_Buyer = ' ', -- Blank
T_Source_Grp = ' ', -- Blank
T_Pay_Date = 'YYMMDD' -- Blank
T_ABCD_County = ' ', -- ??? (Blank)
T_COA = ' ', -- Change of Address
T_COA_Date = upd_dte, -- ??? (Blank)
T_DPBC = dlv_cde, -- CDSADR_M/DLV_CDE
T_Mktg_Pos_8 = ' ', -- Blank
T_Mktg_Pos_16 = ' ', -- Blank
T_Mktg_Pos_17 = ' ', -- Blank
T_Mktg_Pos_19 = ' ', -- Blank
T_Mktg_Pos_20 = ' ', -- Blank
T_Child_Age = ' ', -- Blank
T_Expire_Iss_Expd = ' ', -- Blank
T_Order_Date_Exp = '12345678', -- Blank
T_Payment_Date_Exp = '12345678', -- Blank
T_COA_Date_Exp = '12345678', -- Blank
T_Last_Cont_Exp = '12345678', -- Blank
T_Life_Time_Value = '12345678', -- Blank
T_First_Name = atn_1st, -- CDSADR_M/ATN_1ST
T_Mid_Name = atn_mid, -- CDSADR_M/ATN_MID
T_Last_Name = atn_end, -- CDSADR_M/ATN_END
T_Maturity_Code = ctm_sfx, -- CDSADR_M/CTM_SFX
From adv.CDSADR_M;
-- Get Subscription Information
Update T_LRExt1 Set
T_Source = SubStr(key_cde, 1, 07), -- CIRSUB_M/KEY_CDE
T_Rec_Sts = crc_sts, -- CIRSUB_M/CRC_STS
T_Sub_Typ = ' ', -- CIRSUB_M/
T_Paid_Code = bil_sts, -- CirSub_M/Bil_Sts
From adv.CIRSUB_M
Where ctm_nbr = SubStr(T_Acct_Num, 1, 12);
I get an error on SQL that says:
ORA-01747: invalid user.table.column, table.column, or column specification
What am I doing wrong?
Any Other Ideas to do this job?
Thanks,
Lou
|
|
|
|
Re: Update Table with Other Table [message #331163 is a reply to message #331154] |
Wed, 02 July 2008 08:03   |
pcd_lou
Messages: 63 Registered: October 2007 Location: Florida
|
Member |
|
|
Raj,
I looked at that, but I didn't see anything like what I'm trying to do.
Do I have to put a "Select" in each "Set" Statement?
Each Set Column in that code is from the ADR Table. Do I have to match the Customer Number (ctm_nbr) for each column?
Is there an easier way to do this? I need to create an Extract File (Flat) with Address and Subscription information. I create a New Table in the format of the Flat File. I am trying to do a Mass Update from the Adr table, a Mass Update from the Sub table matching by Customer Number and then using the SPOOL command, send the new table to a flat file.
Is this efficient? Is there a better way?
Thanks,
Lou
|
|
|
Re: Update Table with Other Table [message #331165 is a reply to message #331163] |
Wed, 02 July 2008 08:11   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
How about posting the following information. Based on that we will be able to give you some suggestions.
a) No. of records in the source and target table and what percentage you expect to be updated on every run. What is the driving condition for the update?
b) Full version of oracle you are using. For how to obtain this information check the forum guidelines.
c) What is the link between the ADR and subscription table. How it is linked with the parent/to be updated table.
d) How often you run this job (like daily, hourly, monthly etc).
e) Last but not least read the forum guidelines.
Regards
Raj
|
|
|
Re: Update Table with Other Table [message #331170 is a reply to message #331165] |
Wed, 02 July 2008 08:18   |
pcd_lou
Messages: 63 Registered: October 2007 Location: Florida
|
Member |
|
|
Raj,
Thanks for taking an interest.
a) About 450,000 - 500,000 records
b) Oracle 10g Release 10.2.0.3.0
c) Customer Number (ctm_nbr) (unique key) 12 Char
d) Probably run Weekly.
e) I would be happy to. Where do I find the guidelines?
Thanks for helping.
I really need to get a file to them today.
Thanks again,
Lou
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Feb 07 00:07:11 CST 2025
|