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 Go to next message
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 #331154 is a reply to message #331152] Wed, 02 July 2008 07:31 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link for the syntax of update.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10007.htm#SQLRF01708

Please spend some time reading the forum guidelines before posting it.

Regards

Raj
Re: Update Table with Other Table [message #331163 is a reply to message #331154] Wed, 02 July 2008 08:03 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: Update Table with Other Table [message #331185 is a reply to message #331170] Wed, 02 July 2008 08:57 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link. It describes about many real time examples how to update more than million records and been very elaborately explained by Tom with some very good examples.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6407993912330


Quote:
e) I would be happy to. Where do I find the guidelines?

It is the very first link in this forum section.

Regards

Raj
Re: Update Table with Other Table [message #436806 is a reply to message #331152] Mon, 28 December 2009 08:34 Go to previous messageGo to next message
manub22
Messages: 1
Registered: December 2009
Location: India
Junior Member

Hey!
It seems you are using Advantage CRM.

Which version you are using and for which company you are working?

Thanks,
MP
Re: Update Table with Other Table [message #436817 is a reply to message #436806] Mon, 28 December 2009 12:17 Go to previous message
joy_division
Messages: 4616
Registered: February 2005
Location: East Coast USA
Senior Member
Just note that you are responding to a 1.5 year old message.
Previous Topic: query to get year,month and days from dob
Next Topic: How to get the table names present in Package/Procedure/Functions
Goto Forum:
  


Current Time: Tue Sep 27 17:55:38 CDT 2016

Total time taken to generate the page: 0.17613 seconds