Updating a table from another table in oracle

It is needed because if this CONSTRAINT is not there then it will result in multiple rows which will create an ambiguous situation.drop table a_upd; drop table b_upd; create table a_upd (id number(10) not null, value varchar2(32), constraint a_upd_pk primary key (id)); create table b_upd (id number(10) not null, value varchar2(32)); insert into a_upd(id,value) values (1,'a'); insert into a_upd(id,value) values (2,'aa'); insert into a_upd(id,value) values (3,'aaa'); insert into a_upd(id,value) values (4,'aaaa'); insert into a_upd(id,value) values (5,'aaaaa'); insert into b_upd(id,value) values (1,'b'); insert into b_upd(id,value) values (3,'bbb'); insert into b_upd(id,value) values (5,'bbbbb'); commit; update (select a.value a_value,b.value b_value from a_upd a, b_upd b where a.id=b.id) set a_value=b_value update (select a.value a_value,b.value b_value from a_upd a, b_upd b where a.id=b.id) set a_value=b_value Error at line 16 ORA-01779: cannot modify a column which maps to a non key-preserved table In this example we can see that it is possible to update records in table b_upd with values from table a_upd.I am able to choose the max “Eff Date” record for each employee from Table A but somehow not able to updated the same “Desc” in “Table B”. This: ******************** Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. STRING('A',1)) FROM DUAL CONNECT BY LEVELSET PAGESIZE 1000 SET LINESIZE 150 ALTER SESSION SET STATISTICS_LEVEL='ALL'; ALTER SYSTEM FLUSH BUFFER_CACHE; ALTER SYSTEM FLUSH BUFFER_CACHE; SPOOL C:\CHECKTIMING. If I remember correctly, your posts on comp.databases.oracle.* are what finally pushed me to start learning about analytic functions a couple years ago.Both tables can also be joined with a common id field.I would like to update the "Location Code" values in one table with the the "Location Code" of the other table. It is impractical for me to update one row at a time. found the solutions...actually if the tables lie in different dbs then create temp table in db X that contain the data of table B from db Y this way our both tables now in same db as X now simply we can update from one table to another as usual Varinder Sandhu, found the solutions...actually if the tables lie in different dbs then create temp table in db X that contain the data of table B from db Y this way our both tables now in same db as X now simply we can update from one table to another as usual found the solutions...actually if the tables lie in different dbs then create temp table in db X that contain the data of table B from db Y this way our both tables now in same db as X now simply we can update from one table to another as usual hi 999baz,it depends, doing this update you will lock a big chunk of data. GROUPING) IN ( SELECT EMP_NO, GROUPING FROM (SELECT EMP_NO, GROUPING, ROW_NUMBER() OVER (PARTITION BY EMP_NO, GROUPING ORDER BY EFF_DATE DESC) RN, DESCR FROM T1) WHERE RN=1); 2 rows updated. GROUPING) IN ( SELECT EMP_NO, GROUPING FROM (SELECT EMP_NO, GROUPING, ROW_NUMBER() OVER (PARTITION BY EMP_NO, GROUPING ORDER BY EFF_DATE DESC) RN, DESCR FROM T1) WHERE RN=1); SELECT * FROM TABLE(DBMS_XPLAN. If that is true, then both, row_number and first/last can pick up any descr from the set of rows, in this case the sort key should be extended to identifiy rows uniquely.

it solved my problem to some i want to know can we update multiple values at the same time using a single querry. Take a look at this example:update ( select profit_bonus, merit_bonus from employee_bonus b inner join employees e on b.employee_id = e.employee_id where e.bonus_eligible = 'N' ) t set t.profit_bonus = 0, t.merit_bonus = 0; Hi David, This time where the employee is eligible for a bonus :-)update ( select b.bonus, e.salary from employee_bonus b inner join employees e on b.employee_id = e.employee_id where e.bonus_eligible = 'Y' ) t set t.bonus = 0.1 * t.salary Hope this helps!

In this case it is true but is is not guaranteed, there is not any constraint that will ensure Oracle that update is safe. Syntax is something I use everyday and there is nothing I need to remember.

Reason why updating b_upd from a_upd succeeded is that I declared primary key on a_upd table so Oracle knows that for some id there will be exactly one row from a_upd table. If in table b_upd is more than one row with same id Oracle will raise an error . It depends on size of tables and sometimes different approaches can be best choice.

December 17, 2009 In a recent comp.databases.Usenet thread: The following question was asked: I need help with a query which involves the 2 tables defined below. CREATE TABLE T1 ( EMP_NO NUMBER, GROUPING VARCHAR2(5), EFF_DATE DATE, DESCR VARCHAR2(5)); CREATE TABLE T2 ( EMP_NO NUMBER, GROUPING VARCHAR2(5), DESCR VARCHAR2(5)); INSERT INTO T1 VALUES (1234,'CI', TO_DATE('01/01/1989','MM/DD/YYYY'),'X'); INSERT INTO T1 VALUES (1234,'CI', TO_DATE('01/02/2000','MM/DD/YYYY'),'X'); INSERT INTO T1 VALUES (1234,'CI', TO_DATE('01/02/2006','MM/DD/YYYY'),'A'); INSERT INTO T1 VALUES (2345,'AF', TO_DATE('01/01/1990','MM/DD/YYYY'),'X'); INSERT INTO T1 VALUES (2345,'AF', TO_DATE('01/02/2005','MM/DD/YYYY'),'A'); INSERT INTO T2 VALUES (1234,'CI','XNN'); INSERT INTO T2 VALUES (2345,'AF','ANN'); INSERT INTO T2 VALUES (3456,'CI','ANN'); COMMIT; SELECT EMP_NO, GROUPING, EFF_DATE, DESCR FROM T1; EMP_NO GROUP EFF_DATE DESCR ------ ----- --------- ----- 1234 CI 01-JAN-89 X 1234 CI 02-JAN-00 X 1234 CI 02-JAN-06 A 2345 AF 01-JAN-90 X 2345 AF 02-JAN-05 ASELECT EMP_NO, GROUPING, EFF_DATE, ROW_NUMBER() OVER (PARTITION BY EMP_NO, GROUPING ORDER BY EFF_DATE DESC) RN, DESCR FROM T1; EMP_NO GROUP EFF_DATE RN DESCR ------ ----- --------- ---------- ----- 1234 CI 02-JAN-06 1 A 1234 CI 02-JAN-00 2 X 1234 CI 01-JAN-89 3 X 2345 AF 02-JAN-05 1 A 2345 AF 01-JAN-90 2 XSELECT EMP_NO, GROUPING, EFF_DATE, DESCR FROM (SELECT EMP_NO, GROUPING, EFF_DATE, ROW_NUMBER() OVER (PARTITION BY EMP_NO, GROUPING ORDER BY EFF_DATE DESC) RN, DESCR FROM T1) WHERE RN=1; EMP_NO GROUP EFF_DATE DESCR ------ ----- --------- ----- 1234 CI 02-JAN-06 A 2345 AF 02-JAN-05 AUPDATE T2 SET DESCR=( SELECT DESCR FROM (SELECT EMP_NO, GROUPING, ROW_NUMBER() OVER (PARTITION BY EMP_NO, GROUPING ORDER BY EFF_DATE DESC) RN, DESCR FROM T1) T1 WHERE RN=1 AND T1. TXT UPDATE T2 SET DESCR=( SELECT DESCR FROM (SELECT EMP_NO, GROUPING, ROW_NUMBER() OVER (PARTITION BY EMP_NO, GROUPING ORDER BY EFF_DATE DESC) RN, DESCR FROM T1) T1 WHERE RN=1 AND T1. I look forward to seeing your future contributions in the comp.databases.oracle.* groups.

What I need to do is choose the record with the max “Eff Date” from “Table A” for a particular “Emp No.” and update the “Desc” from that record in the field “Desc” of “Table B” for the same “Emp No.”. (More of Maxim’s solutions may be found in the “SQL – Methods of Reformatting into Equivalent Forms” blog series.) ******************** When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes.

Leave a Reply

  1. german handicapped dating 23-Jan-2018 21:43

    Unlike other secret affair websites, all profiles on Affair Hub are real and communicating is always free.

  2. dating salvadoran men 31-Oct-2017 08:14

    The Kingdom of Saudi Arabia (KSA) continues to undertake engineering efforts to allow for an even greater number of pilgrims.