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
I had trouble connecting my External hard drive (My passport WD) with my macbook. It worked before but not now. The external hard disk is blinking and is spinning but just not showing up on my computer. I tried looking in disk utility and no theres n [More]
I have installed Poryal EP 6 SP14 with SID name as PRD. We have SAP R/3 also with the SID name as PRD. After the installation of the Portal we were testing SSO with SAP R/3 PRD after I imported certificate to sap R/3 PRD using STRUSTSSO2. SSO is not [More]
How do I change the fonts on the home page? They recently shrank from 12 to very tiny..(6 pt?). I did nothing to change them, but after the event tried to change them using "preferences-Fonts"which had no effect. ATT seems to have made a change [More]
CAN I MAKE PDF'S FROM AUTOCAD DWGS. USING ACROBAT 9, WAS USING ACROBAT 11 AND COULD PLOT TO PDF FROM AUTOCAD MENU, DON'T HAVE THAT OPTION WITH 9Newer versions of Acrobat's AutoCAD plug-in support newer versions of AutoCAD. Older versions of Acrobat h [More]
I recently took a picture of my friend, and when i uploaded it into iphoto, i clicked on the b&w, and sepia color effects. I determined I did not like them, and now I cannot figure out how to change my photo back to the original color. Please help.ne [More]
Hello I have installed the Oracle SOA Suite 11g on Windows 7 and have a problem with BPM. I followed the steps of the Installing BPM OBE but when I go to http://localhost:7001/bpm/workspace/ I get a 404 error. I don't have any idea why do I have this [More]
Hi,  I am having problems with my Lenovo X200 refusing to go to sleep. When I hit Fn-F4 or choose sleep from the menu, the screen goes black, the computer fan/harddisk noise goes down just as if it is going to sleep, it beeps and the moon lights up. [More]
When I started up my computer today for the first time ever all I saw was a larger image of my hard drive located in the middle of a gray screen. Had to click on it to proceed to the desk top. Has this every happened to anyone else? This is the first [More]
My OS is Vista Ultimate (64-bit). My firewall is Norton360. After automatically upgrading yesterday, my firefox no longer functions/works. I get a firefox screen that is partially enabled, but am not able to open any websites, etc.You can check if yo [More]
Hi all, I have 2 quick questions: - Is there any way where we can do the revaluation at actual prices for service orders? I tried using CON2, but it doesn't work - Suppose i am using valuated Sales Order Stock with control by SO, at which level shoul [More]