Sitemap

Amicuk Programming Answers

Update column in one table from another

2015-10-11   Views:0

Advertisement

I've got two tables: t1: con_num con_code ex elin addressee a few other columns t2: con_num con_code elin addressee contact a few other columns I just got about 300 rows into t2 using sqlloader, and now I need to use t2 to update t1. t1 will have 3 r

I've got two tables:
t1:
con_num
con_code
ex
elin
addressee
a few other columns
t2:
con_num
con_code
elin
addressee
contact
a few other columns
I just got about 300 rows into t2 using sqlloader, and now I need to use t2 to update t1. t1 will have 3 rows per ex||elin, but t2 can have more or less rows per elin (which is the same as ex||elin in t1). If t2 has 4 rows per one elin, then that is 4 distinct pieces of data, and I need to make sure that t1 reflects 4 rows in its ex||elin, each containing the addressee||contact from t2. So I'll need to add a row to t1, as well as update 3 rows. Is this possible with just SQL, or do I need to write PL/SQL?
The following is the code I've been trying:
update t1
set t1.addressee =
(select addressee from t2
where t2.con_num = 'number'
and t1.con_code = t2.con_code
and t1.ex ||t1.elin_num = t2.elin_num)
This code is not correct, it returns more than one row from t2. Also, I'd need to concatenate contact onto addressee with a space in the middle, not just addressee, into t1.addressee.
Any ideas, pointers?
Thanks!

The replay answer
Advertisement
Forgot some info: Ora8.1.5 on win2kpro. Also, I was wanting to combine addressee||contact in the insert/update statement to differentiate one addressee from another in t1 after the data is inserted. We can have several addressees that are the same, but each one goes to a different contact for each record.
Thanks!

Go to See the other 2 answers

How to find the frequently updated columns in a table

Category:DefaultRelease time:2015-10-11Views:130

Hi , I need to find out all the columns of a tables which are frequently getting updated. is there any way that I can achieve this ? I am not sure if any meta data information for DML operations exist in Oracle 9 i. Any input is welcome. Thanks in Ad[More]

OAF-Checkbox when selected should update column in a table

Category:DefaultRelease time:2015-10-11Views:130

Hello, I have a requirement to add checkbox to MES Operator page and it should perform the following, 1) When MES Operator page is opened, it should read attribute1(Y/N) from table and accordingly check/uncheck checkbox. 2) When checkbox is checked/u[More]

Update column in one table from another

Category:DefaultRelease time:2015-10-11Views:130

I've got two tables: t1: con_num con_code ex elin addressee a few other columns t2: con_num con_code elin addressee contact a few other columns I just got about 300 rows into t2 using sqlloader, and now I need to use t2 to update t1. t1 will have 3 r[More]

Fastest way to update column in all tables of schema

Category:DefaultRelease time:-0001-11-30Views:130

In our schema we have two columns ColA and ColB common in all tables in our schema. Suppose these columns have values as below in all tables ColA     ColB      A1     B11      A12     B22      ABC     DEF      Now we have to update ColA and ColB wher[More]

How to update multiple columns from different tables using cursor.

Category:DefaultRelease time:-0001-11-30Views:130

Hi, i have two tables test1 and test2. i want to udpate the column(DEPT_DSCR) of both the tables TEST1 and TEST2 using select for update and current of...using cursor. I have a code written as follows : DECLARE v_mydept1 TEST1.DEPT_CD%TYPE; v_mydept2[More]

Oracle Alert: Access before and after update values of a table column

Category:DefaultRelease time:2015-10-11Views:130

We have a requirement where a notification needs to be sent when 'END_DATE' attribute (column in a table X) is set, in an Oracle Applications form. I have defined an Event based Oracle Alert which fires 'On Update' of the table X. Could anyone please[More]

How to update a BLOB column in a table with a file in local machine?

Category:DefaultRelease time:2015-10-11Views:130

I have a file (of XML type) in my local machine. I want to update it in a BLOB column of a table in databse server. Can anyone help me in this regard. Appreciate your help. Regards Walter Nicholasuser447427 wrote: I have a file (of XML type) in my lo[More]

Update column data to Upper Case in parent and child table

Category:DefaultRelease time:2015-10-11Views:130

Hi , I am facing issue while updating column value to upper case in parent table and child table. How can i do that ? when updating parent row: ORA-02292: integrity constraint (XXXXXXXXXXXXXX_FK) violated - child record found When updatng correspondi[More]

Want to update a column with  different column of same table

Category:DefaultRelease time:2015-10-11Views:130

I have a table TEST_CDR which is having 250 millions records also having partition by hash. there are two column in this table ID and CDR_ID want to update ID in CDR_ID but it take too much time like 24 hours or more. please suggest me a simple way.H[More]

How to update the COST column using another table's column

Category:DefaultRelease time:2015-10-11Views:130

Dear All, I have table: table parts: pno, pname, qoh, price, olevel table orders: ono, cno, eno, received, shipped table odetails: ono, pno, qty view:orders_view: ono, cno, eno, received, shipped,sum(qty*price)order_costview:odetails_view: ono, pno,[More]

Updating a column from other table

Category:DefaultRelease time:-0001-11-30Views:130

Hi all, I have a simple update query problem. I have four tables activist(activist_id,first_name,last_name,c_state), membership(activist_id,g_n_id), group_network(g_n_id,g_n_type_id), school_grop_det(g_n_id,state). For some records in activist table[More]

Hot
Please, please help... I'm having a very strange, very frustrating problem that I believe comes down to the Mail application on my Mac. I am running Snow Leopard (10.6.4) and using three email accounts I access through Mail via POP, and have done so [More]
I came in this morning to work and it seems that the entire office was working at a snails pace (the 4506 that runs the office is in a Flat network config, only 40 devices all in the same domain). Everything runs at Gigabit or 100mbit speeds. It felt [More]
Hi , How do I check the authorization object for any field? I specifically need one for delivery block. Please help. Thanks, ShailajaHi, If your looking to put a delivery level block or its removal then i guess you explore it through userexit mv45afz [More]
Hi,     In my report I am using "OE_ORDER_HEADERS_V" view in my data group query. This view is operating unit(OU) specific so, i am using "USER_EXIT" commands in before report and after report triggers correctly. Despite making above t [More]
Hi, in SE30 (abap runtime analysis) i have tried to possible solutions for a DB access, and according to the performance tuning rules you should use sorted(or hashed) tables in stead of standard table, but in my case the runtime is almost equal. How [More]
Hi, Please help me with an example for loading cost center hierarchy and displaying it in a query. I am thinking of this sample scenario. will this work out ?? In my query output in BEx, I will show a list of company code and some other details. When [More]
Can I use Mobile tv on Nokia E90 Communicator? On o2. Or is it just for Vodaphone users? Ta.I don't recall O2 having a TV service. Only option is BBC iPlayerRead other 17 answers [More]
Hello, I have 2 x 140GB (hw Raid-0 + spare). About 20GB is allocated to Solaris on UFS (standard installation and partitions/slices). I would like to allocate the 120GB space left to ZFS. I found a lot of documentation of how to create a ZFS Pool on [More]
I am still using (and will not be updating) Windows XP. The details of a Firefox update indicate that the new version (which I don't need) may interfere with XP. I want to have Firefox cease and desist prompting me for an update. How can I do this? I [More]
Howdy. I just inherited an iMac G3 with an OS 9.1 operating system. I was wondering if A) there is a way to upgrade the operating system to something a little more modern, or B) you can suggest an internet browser to use. I'm using Internet Explorer [More]