Correction ?
Gary, June 28, 2005 - 8:02 pm UTC
Don't you mean....
update ( select old.old_account_number, new.new_account_number
from old_table old,
mapping_table new
where old.old_account_number = new.old_account_number )
set old_account_number = new_account_number;
(ie in the subquery, match the old account numbers not try matching the old in the old table to the new in the mapping table)
Of course if the original posting had the CREATEs/INSERTS etc, it would have a quick and east test.
June 28, 2005 - 8:46 pm UTC
yes, the predicate in the join for the fictional question without a sample table and data was wrong
(everytime I wing it, try to answer WITHOUT TESTING, it comes out wrong. That means something doesn't it....... test cases are relevant :)
A reader, June 29, 2005 - 2:02 am UTC
Hi Tom,
Thanks for answering my question. You told me to use
update ( select old.old_account_number, new.new_account_number
from old_table old,
mapping_table new
where old.old_account_number = new.old_account_number )
set old_account_number = new_account_number;
But I think my questions still reamin unanswered
1. How to update multiple columns in single SQL ? In your answer only one number say client number will get updated. But Sales Supplier, Purchase supplier, Wholesale Supplier ...etc many other columns how can i update in single SQL ( I have to update around 70 columns and some columns may have null values such as Purchase supplier ...etc )
2. Secondly you have shown me to update table with "KEY PRESERVED" method. If it is not possible can you show me how to write "GOOD" sql ?
regards & thanks
pjp
June 29, 2005 - 8:53 am UTC
select as many old columns as you want and as many new columns as you want???
update ( select old.a, old.b, old.c, ....
new.x, new.y, new.z, ....
from ...
)
set a = x, b = y, c = z, ..............
You need to have the table you are updating from have a unique or primary key constraint. IT NEEDS THIS LOGICALLY regardless of the method used, so just add it (for if a row in OLD joins to more than one row in NEW -- you are hosed, you cannot update this data, therefore NEW has a unique/primary key, add it to facilitate the update)
Update
chithra, September 28, 2005 - 9:46 am UTC
I had a similiar query and i tried applying to my tables. i.e, one column of a table should get updated from another column of another table. But when i did the same with the syntax given i got this error ora-01779 cannot modify a column which maps to a non key preserved value.
Can you please clarify.
September 28, 2005 - 10:29 am UTC
you need to have a unique/primary key constraint on the table you are joining to.
ops$tkyte@ORA10G> create table t1 ( x int, y int );
Table created.
ops$tkyte@ORA10G> create table t2 ( a int, b int );
Table created.
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> update ( select * from t1, t2 where t1.x = t2.a )
2 set y = b;
set y = b
*
ERROR at line 2:
ORA-01779: cannot modify a column which maps to a non key-preserved table
ops$tkyte@ORA10G> alter table t2 add constraint t2_pk primary key(a);
Table altered.
ops$tkyte@ORA10G> update ( select * from t1, t2 where t1.x = t2.a )
2 set y = b;
0 rows updated.
The database wants to know that each row in T1 joins to AT MOST one row in T2.
Tapan Pandya, January 09, 2006 - 2:43 am UTC
More information
Anurag Doshi, March 28, 2006 - 4:46 pm UTC
If you are 2 tables owned by different users, you will need update rights on both the tables even when you are updating only 1 table.
Did not updated my table
Nabeel Khan, April 24, 2008 - 5:13 am UTC
I tried doing the same through this
update ( select old.IDENTIFICATIONNUMBER,
old.CONTACTNO1,
old.CONTACTNO2,
old.CONTACTNO3,
old.CONTACTNO4,
old.CONTACTNO5,
new.IDENTIFICATIONNUMBER,
new.MOBILEPHONE1,
new.MOBILEPHONE2,
new.HOMEPHONE,
new.FAXNUMBER,
new.WORKEXTENSION
from TBL_VOTERS old,
VOTERSWITHNUMBERS new
where old.MOINUMBER = new.MOINUMBER )
set
old.IDENTIFICATIONNUMBER = new.IDENTIFICATIONNUMBER,
old.CONTACTNO1 = new.MOBILEPHONE1,
old.CONTACTNO2 = new.MOBILEPHONE2,
old.CONTACTNO3 = new.HOMEPHONE,
old.CONTACTNO4 = new.FAXNUMBER
old.CONTACTNO5 = new.WORKEXTENSION;
But I was getting following error, by the Im on 10g R2
SQL Error: ORA-00904: "NEW"."WORKEXTENSION": invalid identifier
00904. 00000 - "%s: invalid identifier"
April 28, 2008 - 11:23 am UTC
you do not use correlation names "up", only "down" in a query (eg: down into a correlated subquery)
update ( select old.IDENTIFICATIONNUMBER old_id,
old.CONTACTNO1 old_c1,
old.CONTACTNO2 old_c2,
old.CONTACTNO3 .....,
new.IDENTIFICATIONNUMBER new_id,
new.MOBILEPHONE1 new_m1,
new.MOBILEPHONE2 ...
from TBL_VOTERS old,
VOTERSWITHNUMBERS new
where old.MOINUMBER = new.MOINUMBER )
set
old_id = new_id, .....
Did not updated my table followup
Nabeel Khan, April 24, 2008 - 5:15 am UTC
There is a comma after the second last column in set
Updating rows in table1 matching some rows in table2
Ovidiu, May 05, 2008 - 7:26 am UTC
I use this statement:
update table1 set col1 = col2 + col3
where col4 = 'OK'
and exists (select distinct 1 from table2 where table1.col_x = table2.col_x2 and table2.col_y2 = 'superOK')
This update works, but is too slow...
table1 is huge and table2 is small.
1 row in table1 matches more rows in table2.
Using join in select statement is very fast!
select col2 + col3 as result
from table1
inner join (select distinct col_x2 from table2 where col_y2 = 'superOK') temp on temp.col_x2 = col_x1
where col4 = 'OK'
How can I use join in my update statement?
I think it will work faster.
Thanks!
May 05, 2008 - 10:23 am UTC
what about
update table1 set col1 = col2 + col3
where col4 = 'OK'
and col_x in (select col_x2 from table2 col_y2 = 'superOK')
update
A reader, May 05, 2008 - 2:11 pm UTC
books
------
bkno PK
bk_flag
ORDERED_ITEMS
--------
order_date <PK>
cust_id <PK>
bkno <PK>
qty
I want to update ORDERED_ITEMS table for a few records but exclude any book where BOOKS.bk_flag='X'
Would you do this
UPDATE ordered_item SET
qty = new_qty WHERE order_date = xxxx and cust_id = xxxx
and bkno not in (select bkno from books where bk_flag <> X)
or would you do an update on a JOIN.
May 06, 2008 - 12:42 am UTC
tell me how you would do an update on a join here?
you update a join when you want the data from T1 to overwrite what is in T2 and you join T2 to T1 by T1's primary key.
Here you want to set a qty to a constant for some records - and you 'anti-join' to the other table, not join.
this is 'correct' as it is.
Update Table
Dawar, May 05, 2008 - 7:05 pm UTC
SQL> create table jobs
2 (job_no number(4),
3 title varchar2(50),
4 rate_type varchar2(1),
5 flat_rate_amt number(8,2),
6 max_salary number(8,2));
SQL> create table class_test
2 (JOB_NO NUMBER(4),
3 MIN_RANGE VARCHAR2(14),
4 MAX_RANGE_CNTRL VARCHAR2(14) );
SQL> create table types
2 (type varchar2(1),
3 description varchar2(50),
4 factor number(4));
************************************************************************
Insert table:
insert into types values ('A','Monthly','12')
/
insert into types values ('B','HOURLY','1796')
/
insert into types values ('G','Daily','224')
/
insert into types values ('H','Per Visit','')
/
insert into types values ('I','Per session','')
/
********************************************************************
insert into class_test values ('0001','3,281.18','4,177.64')
/
insert into class_test values ('0002','3,779.27','4,820.00')
/
insert into class_test values ('0003','4,779.27','5,820.00')
/
insert into class_test values ('0004','5,779.27','6,820.00')
/
insert into class_test values ('0005','7,311.45,'10,402.64')
/
insert into class_test values ('0005','','20.83')
/
insert into class_test values ('0006','','14,528.60')
/
insert into class_test values ('0007','5,001.82','5,727.91')
/
******************************************************************
insert into jobs values ('0001','CSI','A','','50131.68')
/
insert into jobs values ('0002','TTI','A','','200')
/
insert into jobs values ('0003','SSP','A','','')
/
insert into jobs values ('0004','CCC','A','','')
/
insert into jobs values ('0006','CIO','A','','')
/
insert into jobs values ('0007','CIO','A','','')
/
It should be update rows in JOBS table
where rate_type = 'A',
and ITEM_NO of JOBS table should be match with ITEM_NO of class_test table.
update jobs
set MAXIMUM_ANNUAL_SALARY =TYPES.FACTOR * CLASS_TEST.MAX_RANGE_CNTRL
where jobs.rate_type = 'A' and
jobs.ITEM_NO = CLASS_TEST.ITEM_NO
5 ;
jobs.ITEM_NO = CLASS_TEST.ITEM_NO
*
ERROR at line 4:
ORA-00904: "CLASS_TEST"."ITEM_NO": invalid identifier
Any help will be appreciated.
D
May 06, 2008 - 1:07 am UTC
and the types table plays into this HOW?
where are your primary keys? You want to join basically three tables together, I can envision that jobs.item_no = class_test.item_no, but see NO WAY to add in TYPES to that join
this question begs more questions than answers itself.
Updating rows in table1 matching some rows in table2
Ovidiu, May 06, 2008 - 5:03 am UTC
Thanks for your input!
The problem was:
table2 (~5000 rows) was not analyzed...
After
analyze table table2 compute statistics
everything is ok.
The original update works very fast now!
Thanks again!
Pedro, May 14, 2008 - 10:22 am UTC
Hello Tom.
I have a problem similar to this, but probably a little simpler, but I can't make it to work
I have 2 tables, one having a subset of columns the other has, i.e.:
Table A
c1 <PK>
c2
c3
c4
c5
Table A_Extended
nc1 <PK>
nc2
nc3
nc4
nc5
nc6
nc1 matches c1, nc2 matches c2, nc3 matches c3
What I need to do is update particular records from one of the tables, but only the common columns
The conceptual SQL should be some in the likes of (this is not a query don't hit me ;) it's what is intended to be done):
Update table_extended
set table_extended.nc2 = table.c2,
table_extended.nc3 = table.c3
where table_extended.nc1 = table.c1 = @parameter
Is there a simple way of doing this update?
May 14, 2008 - 3:39 pm UTC
I did not follow this - sorry, not sure what you need.
Pedro, May 15, 2008 - 8:36 am UTC
Ok, going to try and explain better with an example:
Table A
ID <PK>
Name
Marital Status
Age
Number of kids
Table B
ID <PK>
Name
Marital Status
Mobile Phone
Email
I want to update table A (Name and Marital Status) using table B data, considering that ID matches in both tables, and using a designated ID (the use of Id may be optional, but for sake of simplicity lets assume we only want to update 1 row, but multiple columns at same time)
Update table A
set table A.Name = table B.Name,
table A.Marital Status = table B.Marital Status
where table A.ID = table B.ID = @parameter
Hope I succeded in making it clearer.
May 19, 2008 - 12:03 pm UTC
update ( select a.marital_status a_ms, a.name a_nm,
b.marital_status b_ms, b.name b_nm
from a, b
where a.id = b.id )
set a_ms = b_ms, a_nm = b_nm;
*exactly* like the original answer way up on the top of the page.
Just join
Using exists
Tilak, May 28, 2008 - 7:11 am UTC
If i am updating & using non key preserved tables
then exists seems to be quicker one!
But if i update millions is there any other way ?
update account_allocation2
set (sup_hyg_acc_policy,sup_hyg_acc_add)=
( select sup_hyg_acc_policy,sup_hyg_acc_add
from acc_hyg_driver where acc_hyg_driver.account_number =
account_allocation2.account_number
and exists ( select '1' from acc_hyg_driver where acc_hyg_driver.account_number =
account_allocation2.account_number ))
any ideas?? to make it quicker!
May 28, 2008 - 9:45 am UTC
merge into account_allocation2
using acc_hyg_driver
on (acc_hyg_driver.account_number =
account_allocation2.account_number)
when matched
then update set sup_hyg_acc_policy = acc_hyg_driver.sup_hyg_acc_policy,
sup_hyg_acc_add = acc_hyg_driver.sup_hyg_acc_addif you are on 9i, you'll need:
merge into account_allocation2 <b>
using (select * from acc_hyg_driver where account_number in (select account_number from account_allocation2) acc_hyg_driver</b>
on (acc_hyg_driver.account_number =
account_allocation2.account_number)
when matched
then update set sup_hyg_acc_policy = acc_hyg_driver.sup_hyg_acc_policy,
sup_hyg_acc_add = acc_hyg_driver.sup_hyg_acc_add
<b>when not matched then insert (account_number) values ( null )</b>the "using" query only needs to use the IN if acc_hyg_driver might have account numbers that are NOT IN account_allocation2, if acc_hyp_driver only has account numbers in that set, you can just use that table.
Tilak, May 29, 2008 - 11:46 am UTC
Excellent!
But the volumes dealt with are
account_allocation has 16 million
acc_hyg_driver has 6 million
and the update does around 6 million records,
The current merge takes more than 5 hours with other bottle necks ( includes CPU sharing) which cant be helped!
Is it better to go for Create Table as Select (update info) ????
Many thanks,
May 29, 2008 - 1:10 pm UTC
and if the other bottlenecks exist for merge, they shall exist for create table as select - plus reindex - plus grant - plus constrain
what are your waits for, got a tkprof with waits?
Tilak, June 04, 2008 - 11:59 am UTC
Create Table works quicker! (no over heads for that!)
But i cant use the create for limitations on downtime of the table.
Dont want the table to be dropped and recreated.
Unfortunately i cant get the tkprof as i dont have access to.
Is there any other way i can use to speed up the update by any efficient means!
Interestingly the Update that is suggested by you takes 12 hours and Update using the PL/SQL cursor style takes 8 hrs
consistently on the live environment!
any suggestions or code that i can try?
thanks in advance
June 04, 2008 - 12:03 pm UTC
create table has to be followed by indexing, grants, constraints and everything else - have you accounted for THAT.
...
Unfortunately i cant get the tkprof as i dont have access to.
....
if you were asked to pilot a plane with a blindfold on - how well do you think you would do.
no inputs = no outputs.
I cannot tell you how to make something more efficient if I do not know what is impeding the efficiency in the first place.
another update
A reader, September 25, 2008 - 9:01 pm UTC
Tom,
Is there workaround for the following?
Thank you.
SQL> create table t1 (x int, y int);
Table created.
SQL> alter table t1 add constraint t1_pk primary key (x);
Table altered.
SQL> drop table t2;
Table dropped.
SQL> create table t2 (a int, b int);
Table created.
SQL> alter table t2 add constraint t2_pk primary key (a,b);
Table altered.
SQL> update ( select * from t1, t2 where t1.x = t2.a )
2 set y = b;
set y = b
*
ERROR at line 2:
ORA-01779: cannot modify a column which maps to a non key-preserved table
September 26, 2008 - 1:11 pm UTC
you can use merge
merge into t1
using t2
on (t1.x = t2.a)
when matched then update set y = t2.b;
if there exist duplicated by t2.a (more than one row with t2.a having the same value), the merge will fail at runtime when it encounters you trying to modify Y in a non-deterministic fashion.
ops$tkyte%ORA10GR2> create table t1 ( x int, y int );
Table created.
ops$tkyte%ORA10GR2> create table t2 ( a int, b int );
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t1 values ( 1, null );
1 row created.
ops$tkyte%ORA10GR2> insert into t2 values ( 1, 2 );
1 row created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> merge into t1
2 using t2
3 on (t1.x = t2.a)
4 when matched then update set y = t2.b;
1 row merged.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t2 values ( 1, 3 );
1 row created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> merge into t1
2 using t2
3 on (t1.x = t2.a)
4 when matched then update set y = t2.b;
merge into t1
*
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables
Update from other table
geoff, October 06, 2008 - 12:49 am UTC
Dear Tom,
You mentioned previously that we need to have the table we are updating from to have a unique or primary key constraint. What if i need to update a table using data from an external table?The way i understand it external tables don't have primary keys. I don't think i can use the merge statement here since there are more than 1 condition that has to be met for the target table to be updated. Any ideas? Thanks very much!
October 06, 2008 - 2:50 pm UTC
use merge
merge into real_table
using external_table
on (join condition)
when matched then update.....;
Update using external tables
geoff, October 06, 2008 - 11:24 pm UTC
Hi Tom!
thank you very much for the quick response!I have followup questions:
1.Can we use the WHERE clause in the merge statement? my oracle version is 9i.
2.Can we put several conditions in the ON clause?
And why do i get an 'invalid column name' error when i run this?
UPDATE ORD_DTL D1
SET
D1.OD_ITEM_TYPE=EXT_URIN_DTL_RESULT.OD_ITEM_TYPE
D1.OD_ITEM_PARENT=EXT_URIN_DTL_RESULT.OD_ITEM_PARENT
D1.OD_ORDER_TI=EXT_URIN_DTL_RESULT.OD_ORDER_TI
D1.OD_PKG_ORDER=EXT_URIN_DTL_RESULT.OD_PKG_ORDER
D1.OD_SEQ_NO=EXT_URIN_DTL_RESULT.OD_SEQ_NO
D1.OD_TR_VAL=EXT_URIN_DTL_RESULT.OD_TR_VAL
D1.OD_TR_UNIT=EXT_URIN_DTL_RESULT.OD_TR_UNIT
D1.OD_TR_FLAG=EXT_URIN_DTL_RESULT.OD_TR_FLAG
D1.OD_TR_RANGE=EXT_URIN_DTL_RESULT.OD_TR_RANGE
D1.OD_NORMAL_LOLIMIT=EXT_URIN_DTL_RESULT.OD_NORMAL_LOLIMIT
D1.OD_NORMAL_UPLIMIT=EXT_URIN_DTL_RESULT.OD_NORMAL_UPLIMIT
D1.OD_PANIC_LOLIMIT=EXT_URIN_DTL_RESULT.OD_PANIC_LOLIMIT
D1.OD_PANIC_UPLIMIT=EXT_URIN_DTL_RESULT.OD_PANIC_UPLIMIT
D1.OD_TR_COMMENT=EXT_URIN_DTL_RESULT.OD_TR_COMMENT
D1.OD_01_VAL=EXT_URIN_DTL_RESULT.OD_01_VAL
D1.OD_02_VAL=EXT_URIN_DTL_RESULT.OD_02_VAL
D1.OD_03_VAL=EXT_URIN_DTL_RESULT.OD_03_VAL
D1.OD_TEST_GRP=EXT_URIN_DTL_RESULT.OD_TEST_GRP
D1.OD_SPL_TYPE=EXT_URIN_DTL_RESULT.OD_SPL_TYPE
D1.OD_DATA_TYPE=EXT_URIN_DTL_RESULT.OD_DATA_TYPE
D1.OD_ORDER_ITEM=EXT_URIN_DTL_RESULT.OD_ORDER_ITEM
D1.OD_ANZ_ORDER=EXT_URIN_DTL_RESULT.OD_ANZ_ORDER
D1.OD_WC_CODE=EXT_URIN_DTL_RESULT.OD_WC_CODE
D1.OD_LOGNO=EXT_URIN_DTL_RESULT.OD_LOGNO
D1.OD_RESULT_SRC=EXT_URIN_DTL_RESULT.OD_RESULT_SRC
D1.OD_ACTION_FLAG=EXT_URIN_DTL_RESULT.OD_ACTION_FLAG
D1.OD_EDIT_FLAG=EXT_URIN_DTL_RESULT.OD_EDIT_FLAG
D1.OD_STATUS=EXT_URIN_DTL_RESULT.OD_STATUS
D1.OD_VALIDATE_BY=EXT_URIN_DTL_RESULT.OD_VALIDATE_BY
D1.OD_VALIDATE_ON=EXT_URIN_DTL_RESULT.OD_VALIDATE_ON
D1.OD_ANZ_ID=EXT_URIN_DTL_RESULT.OD_ANZ_ID
D1.OD_ANZ_RACKNO=EXT_URIN_DTL_RESULT.OD_ANZ_RACKNO
D1.OD_ANZ_TUBENO=EXT_URIN_DTL_RESULT.OD_ANZ_TUBENO
D1.OD_UPDATE_BY=EXT_URIN_DTL_RESULT.OD_UPDATE_BY
D1.OD_UPDATE_ON=EXT_URIN_DTL_RESULT.OD_UPDATE_ON
D1.OD_ANZ_COMMENT=EXT_URIN_DTL_RESULT.OD_ANZ_COMMENT
D1.OD_CTL_FLAG1=EXT_URIN_DTL_RESULT.OD_CTL_FLAG1
D1.OD_CTL_FLAG2=EXT_URIN_DTL_RESULT.OD_CTL_FLAG2
WHERE D1.OD_TNO=EXT_URIN_DTL_RESULT.OD_TNO
AND D1.OD_TESTCODE=EXT_URIN_DTL_RESULT.OD_TESTCODE
AND D1.OD_CTL_FLAG2 IS NULL
AND EXT_URIN_DTL_RESULT.OD_ACTION_FLAG = 'R';
this is related to my question, this is why i did not use MERGE because i have several conditions for the update.Error points to line 42,invalid column name, even if the column does exist (EXTERNAL TABLE)and i did not misspell it either :).
THANKS!
October 07, 2008 - 1:17 pm UTC
1) where do you want to use this where clause.
update t
using (select * from t2 where ... )
on (joins)
2) the documentation would be very telling wouldn't it? The merge statement is fully documented... (answer = yes, you can)
as for the last one - because you have an invalid column name?? I don't know, I don't have *your tables*
whittle your example down, if it is on line 42, get rid of 90% of the lines before line 42 - then add them back in until it fails again - then you'll find your syntax error or mistake.
Dan, October 08, 2008 - 10:40 am UTC
"this is related to my question, this is why i did not use MERGE because i have several conditions
for the update.Error points to line 42,invalid column name, even if the column does exist (EXTERNAL
TABLE)and i did not misspell it either :)."
You aren't using commas in your update, Oracle thinks you are updating just one thing and isn't recognizing the massive block.
UPDATE ORD_DTL D1
SET
D1.OD_ITEM_TYPE=EXT_URIN_DTL_RESULT.OD_ITEM_TYPE
D1.OD_ITEM_PARENT=EXT_URIN_DTL_RESULT.OD_ITEM_PARENT
D1.OD_ORDER_TI=EXT_URIN_DTL_RESULT.OD_ORDER_TI
...
Should be:
UPDATE ORD_DTL D1
SET
D1.OD_ITEM_TYPE=EXT_URIN_DTL_RESULT.OD_ITEM_TYPE,
D1.OD_ITEM_PARENT=EXT_URIN_DTL_RESULT.OD_ITEM_PARENT,
D1.OD_ORDER_TI=EXT_URIN_DTL_RESULT.OD_ORDER_TI,
...
October 08, 2008 - 10:14 pm UTC
thanks, to be honest, if I cannot run it in sqlplus these days myself - I don't even look at it.
It someone does not take a couple of minutes to develop a small test case - I'm not sure why I should spend minutes compiling their code in my head anymore :)
funny thing, if they would have done this:
whittle your example down, if it is on line 42, get rid of 90% of the lines before line 42 - then add them back in until it fails again - then you'll find your syntax error or mistake.
which is what I would have to do - they would have discovered this all by themselves...
something I don't understand
Piotr, November 03, 2008 - 2:20 pm UTC
Hello Tom,
I've been using join updates for some time, but recently
I run into an unexplained behavior from Oracle. Here is
a short demonstration (I'm using 10.2.0.4 on Windows)
create table x3 (
id number ,
val number,
constraint pk_x3 primary key (id)
);
create table x4 (
id number,
val number,
constraint pk_x4 primary key (id)
);
insert into x3 values (1,55);
insert into x3 values (2,56);
insert into x4 values (1,57);
insert into x4 values (2,58);
-- this works fine:
update (
select x3.val, x4.val new_val
from x3 inner join x4 on x3.id=x4.id
) set val=new_val;
> 2 rows updated
-- but this produces an error:
update (
select x3.val, x4.val new_val
from x3 inner join x4 on x3.id=1 and x4.id=1
) set val=new_val;
> SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table
Is there any reason for that? Does it mean, that in order
to be able to use join updates, we need to build the join
conditions only based on the values in both tables, and
cannot use directly provided values?
You may ask for a real-world scenario to use such syntax.
In my case, I have a table that contains items that belong
to a certain class, and the table has a PK based on two
columns: (CLASS_ID, ITEM_CODE).
Then I want to update rows with the same ITEM_CODE from
one class, with rows of a matching ITEM_CODE, but from
a different class, so the statement looks like this:
update (
select ...
from items c1 inner join items c2
on c1.item_code=c2.item_code
and c1.class_id= <value1>
and c2.class_id= <value2>
) set ....
Which produces the ORA-01779 error for exactly the same
reasons, as in the simplified example above.
I have a workaround, so this is not a big problem, but I'm just wondering if there is any reason for such situation?
Best regards,
Piotr
November 11, 2008 - 11:43 am UTC
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14231/views.htm#sthref3058 it has always been a restriction of the join update - you need to join the table you are updating (t1) to another table (t2) by the primary key/unique constraint defined on t2.
ops$tkyte%ORA10GR2> create table t1(x int, y int);
Table created.
ops$tkyte%ORA10GR2> create table t2(a int, b int );
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> update(select * from t1,t2 where t1.x = t2.a) set y = b;
update(select * from t1,t2 where t1.x = t2.a) set y = b
*
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table
ops$tkyte%ORA10GR2> alter table t2 add constraint t2_pk primary key(a);
Table altered.
ops$tkyte%ORA10GR2> update(select * from t1,t2 where t1.x = t2.a) set y = b;
0 rows updated.
ops$tkyte%ORA10GR2> alter table t2 drop constraint t2_pk;
Table altered.
ops$tkyte%ORA10GR2> merge into t1 using t2 on (t1.x = t2.a)
2 when matched then update set y = b;
0 rows merged.
as demonstrated, Merge has no such restrictions.
Updating multiple colum
A reader, November 20, 2008 - 7:40 am UTC
Update TableName A
SET (FLD1,FDL2,FLD3) = (Select FLD1,FLD2,FLD3 from TableX),
FLD4 = 'X'
WHere
Exists (Select 'X' from TableNAme B)
if the following query return Zero rows
Select FLD1,FLD2,FLD3 from TableX
I need to use another query
Select FLD1,FLD2,FLD3 from TableABC
How can incorporate this in Update statement.
I tried using Coalesce but it didn't work.....
November 24, 2008 - 12:59 pm UTC
ops$tkyte%ORA10GR2> create table t1 ( key int primary key, x int, y int, z int );
Table created.
ops$tkyte%ORA10GR2> create table t2 ( key int primary key, x int, y int, z int );
Table created.
ops$tkyte%ORA10GR2> create table t3 ( key int primary key, x int, y int, z int );
Table created.
ops$tkyte%ORA10GR2> exec dbms_stats.set_table_stats( user, 'T1', numrows => 1000000 );
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> exec dbms_stats.set_table_stats( user, 'T2', numrows => 1000000 );
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> exec dbms_stats.set_table_stats( user, 'T3', numrows => 1000000 );
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t1 select rownum, 100, 100, 100 from all_users where rownum <= 6;
6 rows created.
ops$tkyte%ORA10GR2> insert into t2 select rownum, rownum, rownum, rownum from all_users where rownum <= 3;
3 rows created.
ops$tkyte%ORA10GR2> insert into t3 select rownum+2, rownum+20, rownum+20, rownum+20 from all_users where rownum <= 3;
3 rows created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from t1;
KEY X Y Z
---------- ---------- ---------- ----------
1 100 100 100
2 100 100 100
3 100 100 100
4 100 100 100
5 100 100 100
6 100 100 100
6 rows selected.
ops$tkyte%ORA10GR2> select * from t2;
KEY X Y Z
---------- ---------- ---------- ----------
1 1 1 1
2 2 2 2
3 3 3 3
ops$tkyte%ORA10GR2> select * from t3;
KEY X Y Z
---------- ---------- ---------- ----------
3 21 21 21
4 22 22 22
5 23 23 23
ops$tkyte%ORA10GR2> update t1
2 set (x,y,z) = (select x,y,z from t2 where t2.key = t1.key
3 union all
4 select x,y,z from t3 where t3.key = t1.key
5 and not exists (select null from t2 where t2.key = t1.key)
6 )
7 where exists (select x,y,z from t2 where t2.key = t1.key
8 union all
9 select x,y,z from t3 where t3.key = t1.key
10 and not exists (select null from t2 where t2.key = t1.key)
11 )
12 /
5 rows updated.
ops$tkyte%ORA10GR2> select * from t1;
KEY X Y Z
---------- ---------- ---------- ----------
1 1 1 1
2 2 2 2
3 3 3 3
4 22 22 22
5 23 23 23
6 100 100 100
6 rows selected.
Multiple Column Update based on indivisual conditions
A Reader, April 29, 2009 - 6:42 am UTC
Hi Tom,
Can multiple columns be updated based on different conditions,we have some Type - I columns which needs to be modified if they have changed wrt. to the Delta.
D represents a table
D_DELTA has the changes
X and Y are the Type - I changes, and we need to update based on the WHERE CLAUSE
(a.X <> b.X or ((a.x is null and b.x is not null) or (a.x is not null and b.x is null)))
MERGE INTO D a
USING D_Delta b
ON (a.id = b.id)
When Matched THEN
Update Set a.X = b.x WHERE (a.X <> b.X or ((a.x is null and b.x is not null) or (a.x is not null and b.x is null))),
a.y = b.y WHERE (a.Y <> b.Y or ((a.y is null and b.y is not null) or (a.y is not null and b.y is null)))
It is now working
Any insight how to solve it will be appreciated
April 29, 2009 - 9:47 am UTC
"type - I columns"
"wrt"
??
but.... You cannot conditionally update, you either will update a column in a given update/merge statement or you will not.
The approach would be:
merge into d
using d_delta
on (d.id = d_delta.id)
when matched then update set d.x = d_delta.x, d.y = d_delta.y;
The only other option would be *two* merge/update statements. The savinging in redo generated is unlikely to be worth the extra computing resources to run the statement twice.
Update a table from a table with which it has a many to many relation
Rish G, April 30, 2009 - 2:04 am UTC
Hi Tom,
create table stg_emp (
empno varchar2(6),
request_id number,
event_id number,
start_date date
);
create table emp (
empno varchar2(6),
request_id number,
event_id number,
start_date date,
rec_status varchar2(10)
);
insert into stg_emp
values('ab0001', 123400, 1, to_date('05/01/2009', 'MM/DD/YYYY'));
insert into stg_emp
values('ab0001', 678900, 1, to_date('05/15/2009', 'MM/DD/YYYY'));
insert into stg_emp
values('ab0001', 432100, 1, to_date('06/01/2009', 'MM/DD/YYYY'));
insert into stg_emp
values('ab0001', 987600,2, to_date('06/05/2009', 'MM/DD/YYYY'));
insert into stg_emp
values('ab0001', 342100, 1, to_date('05/20/2009', 'MM/DD/YYYY'));
insert into stg_emp
values('ab0006', 879600, 1, to_date('05/01/2009', 'MM/DD/YYYY'));
insert into emp
values('ab0001', 123400, 1, to_date('05/01/2009', 'MM/DD/YYYY'), 'UNKNOWN');
insert into emp
values('ab0001', 345600, 1, to_date('05/10/2009', 'MM/DD/YYYY'), 'UNKNOWN');
insert into emp
values('ab0001', 342100, 1, to_date('05/20/2009', 'MM/DD/YYYY'), 'UNKNOWN');
Data in the stg_emp is a truncate/insert process from a different source. We have no control over this part.
Constraints
empno + request_id = unique. ( many employees can be assigned to one request_id)
request_id is unique to an event_id. The same request_id cannot belong to different event_ids. One event_id can contain many request_ids.
Here is the requirement
We need to compare the emp table with the stg_emp table and do the following.
1. if empno + request_id + event_id is a match, update the rec_status in emp to NO CHANGE.
2. If only the empno + event_id match and the request_id does not match, and the number of records per empno per event_id is the same in both the tables, then update the table with the data from stg_emp and set the rec_status to UPDATED
3. If the number of records per empno per event_id in emp_stg exceeds the number of records per empno per event_id in emp table, then first update the emp table with the stg_table and set the rec_status to update, followed by an insert into emp from emp_stg;
4. If the number of records per empno per event in emp_stg is less than the number of records per empno per event_id in emp table, then do nothing.
For example :
Records for empno ab0001 with event_id = 1 in emp
ab0001, 123400, 1, 05/01/2009, UNKNOWN
ab0001, 345600, 1, 05/10/2009, UNKNOWN
ab0001, 342100, 1, 05/20/2009, UNKNOWN
records for empno ab0001 with event_id = 1 in stg_emp
ab0001, 123400, 1, 05/01/2009
ab0001, 678900, 1, 05/15/2009
ab0001, 342100, 1, 05/20/2009
ab0001, 432100, 1, 06/01/2009
After the update/insert into emp, the data for empno ab0001 for event_id = 1 should look like this :
ab0001, 123400, 1, 05/01/2009, NO CHANGE
ab0001, 678900, 1, 05/15/2009, UPDATED -- > updated with record from emp_stg
ab0001, 342100, 1, 05/20/2009, NO CHANGE
ab0001, 432100, 1, 06/01/2009, NEW
The entire emp table would look like
ab0001, 123400, 1, 05/01/2009, NO CHANGE
ab0001, 678900, 1, 05/15/2009, UPDATED -- > updated with record from stg_emp
ab0001, 342100, 1, 05/20/2009, NO CHANGE
ab0001, 432100, 1, 06/01/2009, NEW
ab0001, 987600,2,06/05/2009, NEW
ab0006, 123400,1, 05/01/2009, NEW
When the possibility of an insert and an update exists simultaneously, update will be performed on the record with the earlier start date.
As you can see there is no unique way to identify a record to determine which one is to be updated and which is to be considered 'new'.
Requirement 1. is easy to implement :
update emp e
set rec_status = (select 'NO CHANGE'
from emp_stage es
where e.empno = es.empno
and e.request_id = es.request_id
and e.event_id = es.event_id)
where e.empno||e.request_id||e.event_id in (select empno||request_id||event_id from emp_stg);
Requirement 2 and 3 is where the problem is. There is no way to determine a 'new' record vs. an 'updated' record. We cannot determine which record to use from the stg_emp table as an update vs. a new record. Unfortunately, the source system was designed in a different way that works for them. This is what we have to work with and there is no way to set up an identifier in the staging table that can indicate an updated vs. a new record. That is beyond our control.
We cannot set up a primary key on our emp table. Although the combination of empno + request_id is always going to be unique, the request_id associated with an empno is not static because of the said requirement.
Is there a way to do it via a single sql statement ? I tried this update which I now know does not work :
update (
select e_empno, e_event_id, e_reqid, e_recstatus, es_empno, es_event_id, es_reqid
from (
select es.empno es_empno, es.request_id es_reqid, es.event_id es_event_id,
row_number() over (partition by es.empno, es.event_id order by es.start_date) es_rn
from STG_EMP ES
where not exists (select 'x' from emp e1 where e1.empno = es.empno
and e1.event_id = es.event_id
and e1.request_id = es.request_id )
) es,
( select e.empno e_empno, e.request_id e_reqid, e.event_id e_event_id, e.rec_status e_recstatus,
row_number() over (partition by e.empno, e.event_id order by e.start_date) e_rn
from emp E where e.rec_status <> 'NO CHANGE') e
where e_empno = es_empno
and e_event_id = es_event_id
and es_rn= e_rn)
set e_reqid = e_reqid,
e_recstatus = 'UPDATED'
Can you suggest any alternatives? A series of sql statements instead of one single statement will also work. Actually anything that can make this work will be great.
Thanks in advance.
Rish G.
update
A reader, May 22, 2009 - 3:31 pm UTC
Tom:
I replicated your example above for merge in 9iR2 and i got an error. o you know why
1 merge into t1
2 using t2
3 on(t1.x=t2.a)
4* when matched then
update set t1.y = t2.b
* ERROR at line 4:ORA-00905: missing keyword
May 23, 2009 - 10:34 pm UTC
yes, in 9i you had to have a when matched AND a when not matched.
search for
if you are on 9i
on this page for further discussion
update
sam, May 24, 2009 - 6:57 pm UTC
Tom:
I did it this way and it seems to work fine too. any issues with this
create table t1 ( x int, y int )
/
create table t2 ( a int, b int )
/
insert into t1 values ( 1, null )
/
insert into t1 values ( 2,null)
/
insert into t1 values (3,null)
/
insert into t2 values ( 1, 2 )
/
insert into t2 values (2,3)
/
insert into t2 values (3,4)
/
SQL> select * from t1;
X Y
---------- ----------
1
2
3
SQL> select * from t2;
A B
---------- ----------
1 2
2 3
3 4
SQL> Update t1
2 set t1.y = (select t2.b from t2 where t1.x=t2.a);
3 rows updated.
SQL> select * from t1;
X Y
---------- ----------
1 2
2 3
3 4
May 26, 2009 - 8:44 am UTC
well, it is different than updating a join isn't it.
do you see the difference?
consider what happens if you
insert into t1 (x,y) values ( 4,100 );
if you update a join, you'll get one answer, if you use the correlated update you have - you'll get a different one
so you tell us - which is correct for you? That is what it all comes down to.
update
A reader, May 26, 2009 - 1:03 pm UTC
Tom:
I see your point. In my case T2 is the master table and every a values in T2 must have a value in T1.
But i see your point. It will set the 100 to NULL if it could not find a match.
so i did this and it works and it will give you same result. I can't updaet a join because table does not have PK on those columns.
SQL> update t1
2 set t1.y = (select t2.b from t2 where t1.x=t2.a)
3 where x in (select a from t2);
3 rows updated.
SQL> select * from t1;
X Y
---------- ----------
1 2
2 3
3 4
4 100
May 26, 2009 - 4:00 pm UTC
you can use merge of course.
merge into t1
using t2
on (t1.x = t2.a)
when matched then update set y = t2.b;
and typically people would code:
update t1
set t1.y = (select t2.b from t2 where t1.x=t2.a)
where exists (select t2.b from t2 where t1.x=t2.a);
but yours works as well
you do know there should be a unique or primary key constraint on t2.a - if t2.a is not actually unique - failure is yours.
Merge on copy of the same table do not work
Mahendra, June 26, 2009 - 10:52 am UTC
when i run the following merge it is succesful
merge into tk_account_allocation tble using
( select distinct trim(tk.account_number) account_number,tk.open_channel
from tk_phoenix_channel tk,
tk_account_allocation aa
where
trim(tk.account_number)=trim(aa.account_number) ) qry
on ( trim(tble.account_number)=trim(qry.account_number))
when matched then
update set tble.opening_channel=qry.open_channel
when not matched then
insert (tble.ACCOUNT_NUMBER)
values (99999999999099)
whereas i created table of the same table as
create table tk_account_allocation as select * from account_allocation
if i run on the original table account_allocation it comes up with an error as "inserted value too large for the column" when it is not supposed to go into the not matched condition itself
the only difference on the two tables is indexes , does this make any difference in merge??.
many thanks in advance.
June 26, 2009 - 11:27 am UTC
I have no idea what you are doing.
...
whereas i created table of the same table as
create table tk_account_allocation as select * from account_allocation
......
whereas how was it created before when it worked????
please give full examples, this one is so incomplete as to be not useful. give FULL example, showing the error and all.
continued...
Mahendra, June 26, 2009 - 10:54 am UTC
this merge fails
merge into account_allocation tble using
( select distinct trim(tk.account_number) account_number,tk.open_channel
from tk_phoenix_channel tk,
account_allocation aa
where
trim(tk.account_number)=trim(aa.account_number) ) qry
on ( trim(tble.account_number)=trim(qry.account_number))
when matched then
update set tble.opening_channel=qry.open_channel
when not matched then
insert (tble.ACCOUNT_NUMBER)
values (9999999999999999999999999999)
June 26, 2009 - 11:27 am UTC
no creates
no inserts
no look - never.
How I got it to work....
Jeff, March 29, 2010 - 12:45 pm UTC
Because the database table 'personnel_hr', which is maintained by HR drops terminated employees, I maintain my own phone book lookup table of only the employees who have badges (pk) stored in the tables of my web applications.
INSERT INTO personnel_copy copy (badge, fname, lname, mname, title)
SELECT badge, fname, lname, mname, title FROM personnel_hr WHERE badge ='112211'
UPDATE personnel_copy copy SET (badge, fname, lname, mname, title) =
(select badge, fname, lname, mname, title FROM personnel_hr
WHERE badge = '112211')
WHERE copy.BADGE ='112211' ";
These are actually SQL statements in java class files and a badge number is passed in instead of hard coding. When something pertaining to an employee is stored against their badge (my primary key) I also copy (or insert) their phonebook info so if they get terminated, I can still figure out who the employee ID belonged to. Its redundant but I see no other way.
update
prabha, April 19, 2010 - 3:24 am UTC
hi,
i want to update emp table
update the sal of 'raja' with higesh paid of grade 2 transfer him to ranis depts and change the mgr to blake.
April 19, 2010 - 8:55 am UTC
ok, you have my permission to do that.
seriously though - no create table, no inserts, no look - your text description here is not useful to actually start with.
You give no schema
You explain no data
Is raja a name or a group or what?
what is pay grade 2?
how do you transfer people?
You leave us with more questions than answers.
Simple update table.
Rob B, May 05, 2010 - 7:43 am UTC
This will update multiple columns in one table using data from another table.
update table1 t1
set (x1, x2, x3) = (select x1, x2, x3
from table2 t2
where t1.x = t2.x);
Typically t1.x and t2.x are some sort of key fields.
May 06, 2010 - 2:02 pm UTC
that is one way - it updates all rows in the table, setting them to null if they do not exist in the other table.
typically, it would be preferred to do:
update (select t1.x1 t1x2, t1.x2 t1x2, t1.x3 t1x3,
t2.x1 t2x2, t2.x2 t2x2, t2.x3 t2x3,
from t1, t2
where t1.x = t2.x )
set t1x2 = t2x2, t1x3 = t1x3; if your goal was just to update the 'matched' pairs.
Ahmed A.Mageed,Cairo Egypt
Ahmed, June 12, 2010 - 9:23 am UTC
UPDATE INVST_COMPANY set
COMP_LAST_MARKET_PRICE=( SELECT col3 FROM xls01 WHERE INVST_COMPANY.COMP_BLOOMBERG_CODE = xls01.col1),
COMP_LST_MRKT_PRIC_PULL_DT=sysdate,
COMP_LST_MRKT_PRICE_MNUL_PULL='P',
COMP_AMS_52_WKS_H_PRIC=( SELECT COL5 FROM xls01 WHERE INVST_COMPANY.COMP_BLOOMBERG_CODE = xls01.col1),
COMP_AMS_52_WKS_H_PR_DT=( SELECT COL6 FROM xls01 WHERE INVST_COMPANY.COMP_BLOOMBERG_CODE = xls01.col1),
COMP_AMS_52_WKS_L_PRIC=( SELECT COL7 FROM xls01 WHERE INVST_COMPANY.COMP_BLOOMBERG_CODE = xls01.col1),
COMP_AMS_52_WKS_L_PR_DT=( SELECT COL8 FROM xls01 WHERE INVST_COMPANY.COMP_BLOOMBERG_CODE = xls01.col1),
COMP_AMS_6MTH_AVG_TRDNG_VOL=( SELECT col9 FROM xls01 WHERE INVST_COMPANY.COMP_BLOOMBERG_CODE = xls01.col1),
COMP_AMS_MARKET_CAP=( SELECT col11 FROM xls01 WHERE INVST_COMPANY.COMP_BLOOMBERG_CODE = xls01.col1),
COMP_AMS_FREE_FLOAT_PCT=( SELECT SUBSTR(col12,1,INSTR(col12,'%')-1) FROM xls01 WHERE INVST_COMPANY.COMP_BLOOMBERG_CODE = xls01.col1),
COMP_AMS_EPS=( SELECT col13 FROM xls01 WHERE INVST_COMPANY.COMP_BLOOMBERG_CODE = xls01.col1),
COMP_AMS_LTM_P_E_RATIO=( SELECT col14 FROM xls01 WHERE INVST_COMPANY.COMP_BLOOMBERG_CODE = xls01.col1),
COMP_AMS_P_BV_RATIO=( SELECT col15 FROM xls01 WHERE INVST_COMPANY.COMP_BLOOMBERG_CODE = xls01.col1),
COMP_AMS_LST_DIVIDEND_PAID=( SELECT col16 FROM xls01 WHERE INVST_COMPANY.COMP_BLOOMBERG_CODE = xls01.col1),
COMP_AMS_DIVIDEND_FREQ=( SELECT col17 FROM xls01 WHERE INVST_COMPANY.COMP_BLOOMBERG_CODE = xls01.col1),
COMP_AMS_DIVIDEND_YIELD=( SELECT SUBSTR(col18,1,INSTR(col18,'%')-1) FROM xls01 WHERE INVST_COMPANY.COMP_BLOOMBERG_CODE = xls01.col1),
COMP_AMS_STOCK_BETA=( SELECT col19 FROM xls01 WHERE INVST_COMPANY.COMP_BLOOMBERG_CODE = xls01.col1),
COMP_AMS_LISTING_EX=( SELECT col20 FROM xls01 WHERE INVST_COMPANY.COMP_BLOOMBERG_CODE = xls01.col1)
WHERE EXISTS
( SELECT xls01.COL1
FROM xls01
WHERE INVST_COMPANY.COMP_BLOOMBERG_CODE= xls01.col1);
June 22, 2010 - 7:52 am UTC
not sure what your point is, but you have demonstrated perhaps the slowest way to achieve this update of a join.
was that what you wanted to demonstrate? The slowest approach?
Quite Useful
Ravi Gosai, January 25, 2011 - 7:43 pm UTC
The review was quite useful and It was helpful for me to create my Sql statement for update using foreign key and primary key of two tables.
Thank You author for the post.
Outer join in update
phoenixbai, June 17, 2011 - 5:18 am UTC
Hi tom,
I am trying to do a multiple column update as below:
UPDATE DM_F_LO_JCKKB A
SET (A.BY_JE,A.BY_SWL) =
(SELECT
NVL(DECODE(A.JE,0, A.JE, A.JE-NVL(B.JE,0)),0) AS BY_JE,
nvl(DECODE(A.SWL,0, A.SWL, A.SWL-NVL(B.SWL,0)),0) AS BY_SWL
FROM DM_F_LO_JCKKB B
WHERE B.MONTH_ID(+)=A.MONTH_ID-1
AND A.ORG_ID=B.ORG_ID(+)
AND A.AREA_ID=B.AREA_ID(+)
AND A.ITEM_NAME=B.ITEM_NAME(+)
AND A.SHEET_NAME=B.SHEET_NAME(+));
1) the columns used in the WHERE clause can uniquely identify one row.
2) what it is trying to do is that, trying to update the column BY_JE,BY_SWL of (e.g.) April, using below:
BY_JE(APRIL)=JE(APRIL)-JE(MARCH)
BY_SWL(APRIL)=SWL(APRIL)-SWL(MARCH)
The problem with this SQL is that, when there is no corresponding row for MARCH, it fails to update it to the value of APRIL without any subtraction.
It just set it to NULL. it means the OUTER join is not working. The odd thing is that, it works in the SELECT alone, but it don`t when used in UPDATE.
Do you know why?
is there other way to do it?
Thanks and always been a huge fan!
June 17, 2011 - 1:54 pm UTC
that is because when you have an outer join, you need to have TWO tables.
You have but one table in your select. I don't know what you mean by this:
It just set it to NULL. it means the OUTER join is not working. The odd thing
is that, it works in the SELECT alone, but it don`t when used in UPDATE.You have ONE TABLE in your select, as long as there is ONE TABLE - outer join has no "meaning"
ops$tkyte%ORA11GR2> select * from dual d where 'Y' = d.dummy(+);
no rows selected
ops$tkyte%ORA11GR2> with data as (select 'Y' y from dual)
2 select * from dual d, data where data.y = d.dummy(+);
D Y
- -
Y
Your decode is:
nvl(
if a.je = 0
then
return 0
else
return a.je-nvl(b.je)
end if;
, 0 )
If you want me to look at this, I'll need a create and some sample data to play with. I think we need two rows in A and one row in B for a good example.
Outer join in update
PhoenixBai, June 17, 2011 - 9:34 pm UTC
Hi Tom,
CREATE TABLE T (NAME VARCHAR2(10), MONTH_ID NUMBER(6), TOTAL_AMT NUMBER, TOTAL_QTY NUMBER, MONTHLY_AMT NUMBER, MONTHLY_QTY NUMBER);
INSERT INTO T (NAME,MONTH_ID,TOTAL_AMT,TOTAL_QTY) VALUES ('JIMMY',201105,50, 10);
INSERT INTO T (NAME,MONTH_ID,TOTAL_AMT,TOTAL_QTY) VALUES ('JOHN',201103,60, 25);
Insert into T (NAME,MONTH_ID,TOTAL_AMT,TOTAL_QTY) values ('JOHN',201102,50, 15);
I want to set the value for MONTHLY_AMT, MONTHLY_QTY based on the below logic:
MONTHLY_AMT=(TOTAL_AMT OF CURRENT MONTH) - (TOTAL_AMT OF last MONTH);
if there is no record for last month, just set the value of TOTAL_AMT OF CURRENT MONTH to MONTHLY_AMT. Same logic with MONTHLY_QTY.
by using the SELECT, I can get what I want:
SELECT A.NAME, A.MONTH_ID, A.TOTAL_AMT, A.TOTAL_QTY,
NVL((CASE WHEN A.TOTAL_AMT IS NULL THEN NULL
ELSE A.TOTAL_AMT-NVL(B.TOTAL_AMT,0)
END), 0) AS MONTLY_AMT,
NVL((CASE WHEN A.TOTAL_QTY IS NULL THEN NULL
ELSE A.TOTAL_QTY-NVL(B.TOTAL_QTY,0)
END), 0) AS MONTLY_QTY
FROM T B, T A
WHERE A.MONTH_ID-1=B.MONTH_ID(+)
AND A.NAME=B.NAME(+);
name month_id tamt tqty mamt mqty
----------------------------------
JIMMY 201105 50 10 50 10
JOHN 201102 50 15 50 15
JOHN 201103 60 25 10 10
When I try to use the same logic in UPDATE, it is not working:
UPDATE T A
SET (A.MONTHLY_AMT, A.MONTHLY_QTY)=
(SELECT NVL((CASE WHEN A.TOTAL_AMT IS NULL THEN NULL
ELSE A.TOTAL_AMT-NVL(B.TOTAL_AMT,0)
END), 0) AS MONTLY_AMT,
NVL((CASE WHEN A.TOTAL_QTY IS NULL THEN NULL
ELSE A.TOTAL_QTY-NVL(B.TOTAL_QTY,0)
END), 0) AS MONTLY_QTY
FROM T B
WHERE A.MONTH_ID-1=B.MONTH_ID(+)
AND A.NAME=B.NAME(+));
name month_id tamt tqty mamt mqty
----------------------------------
JIMMY 201105 50 10
JOHN 201102 50 15
JOHN 201103 60 25 10 10
So, my question is, is it possible to do it this way? if yes, then where did I do wrong?
How to fix it?
Thank you very much for your instant response.
June 20, 2011 - 9:38 am UTC
ops$tkyte%ORA11GR2> select *
2 from (
3 select rowid rid,
4 name, month_id, total_amt, total_qty, monthly_amt, monthly_qty,
5 total_amt - lag(total_amt) over (partition by name order by month_id) new_monthly_amt,
6 total_qty - lag(total_qty) over (partition by name order by month_id) new_monthly_qty
7 from t
8 )
9 where new_monthly_amt is not null
10 or new_monthly_qty is not null
11 order by name, month_id
12 /
RID NAME MONTH_ID TOTAL_AMT TOTAL_QTY MONTHLY_AMT MONTHLY_QTY NEW_MONTHLY_AMT NEW_MONTHLY_QTY
------------------ ------------------------------ ---------- ---------- ---------- ----------- ----------- --------------- ---------------
AAAWuJAAEAAADwPAAB JOHN 201103 60 25 10 10
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> merge into t
2 using
3 (
4 select *
5 from (
6 select rowid rid,
7 total_amt - lag(total_amt) over (partition by name order by month_id) new_monthly_amt,
8 total_qty - lag(total_qty) over (partition by name order by month_id) new_monthly_qty
9 from t
10 )
11 where new_monthly_amt is not null
12 or new_monthly_qty is not null
13 ) x
14 on ( t.rowid = x.rid )
15 when matched
16 then
17 update set monthly_amt = x.new_monthly_amt, monthly_qty = new_monthly_qty
18 /
1 row merged.
ops$tkyte%ORA11GR2> select * from t order by name, month_id;
NAME MONTH_ID TOTAL_AMT TOTAL_QTY MONTHLY_AMT MONTHLY_QTY
------------------------------ ---------- ---------- ---------- ----------- -----------
JIMMY 201105 50 10
JOHN 201102 50 15
JOHN 201103 60 25 10 10
Anand, June 20, 2011 - 3:29 pm UTC
Try this...
merge into t
using (
select * from
(select name
, month_id
, total_amt
, total_qty
, to_char(add_months( to_date(month_id, 'YYYYMM'), -1), 'YYYYMM') prev_month
, lag(month_id) over (partition by name order by month_id) lg_month
, lag(total_amt) over (partition by name order by month_id) lg_ttl_amt
, lag(total_qty) over (partition by name order by month_id) lg_ttl_qty
from
t) s
where s.lg_month is not null ) stg
on (t.name = stg.name
and t.month_id = stg.month_id
)
when matched then
update set t.monthly_amt = t.total_amt - decode(stg.lg_month, stg.prev_month, lg_ttl_amt, 0)
, t.monthly_qty = t.total_qty - decode(stg.lg_month, stg.prev_month, lg_ttl_qty, 0)
June 20, 2011 - 3:43 pm UTC
why?
if you are going to join the table back to itself, why would you want to do it by name and month_id when you could.... just do it by rowid?
and you don't need the decodes/etc - it can all be done without.
Anand, June 20, 2011 - 3:54 pm UTC
I agree with you on the rowid.
The reason I used the decode - The person posting the question stated,
"MONTHLY_AMT=(TOTAL_AMT OF CURRENT MONTH) - (TOTAL_AMT OF last MONTH);
if there is no record for last month, just set the value of TOTAL_AMT OF CURRENT MONTH to
MONTHLY_AMT. Same logic with MONTHLY_QTY"
June 21, 2011 - 7:35 am UTC
oh, I totally missed that - you are right. I looked at their second set of output with the nulls in it...
Ok, need an NVL
ops$tkyte%ORA11GR2> merge into t
2 using
3 (
4 select *
5 from (
6 select rowid rid,
7 total_amt - nvl(lag(total_amt) over (partition by name order by month_id),0) new_monthly_amt,
8 total_qty - nvl(lag(total_qty) over (partition by name order by month_id),0) new_monthly_qty
9 from t
10 )
11 where new_monthly_amt is not null
12 or new_monthly_qty is not null
13 ) x
14 on ( t.rowid = x.rid )
15 when matched
16 then
17 update set monthly_amt = x.new_monthly_amt, monthly_qty = new_monthly_qty
18 /
3 rows merged.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select * from t;
NAME MONTH_ID TOTAL_AMT TOTAL_QTY MONTHLY_AMT MONTHLY_QTY
------------------------------ ---------- ---------- ---------- ----------- -----------
JIMMY 201105 50 10 50 10
JOHN 201103 60 25 10 10
JOHN 201102 50 15 50 15
Anand, June 20, 2011 - 3:57 pm UTC
By the way Tom - The work you do is amazing. Reminds me of the movie, "Multiplicity"... (reference to the existence of the clones to do all the work you do...)
thank you very much :)
phoenixbai, June 20, 2011 - 8:44 pm UTC
With a little adjustment, this is exactly what I need.
Thank you soooo much as always Tom.
Wish you a great day every day!
Thanks a lot Tom.
A reader, July 29, 2011 - 3:30 pm UTC
Thanks a lot Tom.
Osama Shehab, December 19, 2011 - 1:12 am UTC
Hi Tom,
This is great,
Update
( Select Old.Col1,Old.Col2, ..., New.Col1,New.Col2, ..
Where ..required filters)
Set Old.Col1 = New.Col2,
..
..
but it seems that I must have the update privs. on New table as well? What if the source is a read only view? how can I workaround this.
Thank you in advance
December 19, 2011 - 7:40 am UTC
you can use merge instead.
merge into old
using new
on (old.keys = new.keys)
when matched then update set col1 = new.col1, col2 = new.col2, ....
Rahul, February 01, 2012 - 9:45 am UTC
Hi Tom,
I've same query as you suggested.
Update
( Select Col1,Col2, NewCol1, NewCol2
From A
Where ..required filters ) z
Set z.Col1 = z.Col1 + z.NewCol1,
z.Col2 = z.Col2 + z.NewCol2 ;
There are ~238,000,000 rows in Table 'A'.
When I run SELECT statement separately, it returns ~50,000 rows in 1-2 minutes,
but UPDATE statement takes around 5 hours to execute.
What should be reason ?
Thanks....
February 01, 2012 - 9:53 am UTC
compare the plans, are they different.
and updating 50,000 rows involves updating indexes as well potentially, is there an index update here anywhere?
A reader, February 02, 2012 - 6:57 am UTC
I was wrong in my previous post.
It's actually the SELECT query taking too much time.
As mentioned earlier,
There are ~238,000,000 rows in Table 'A'.
SELECT statement returns ~50,000 rows.
UPDATE statement takes around 5 hours to execute.
Index details are as follow :
PK UNIQUE NORMAL pk_col
INDEX1 NONUNIQUE BITMAP col4, col5, col7, col8
INDEX2 NONUNIQUE BITMAP col4, col5, col15
INDEX3 NONUNIQUE NORMAL col4, col16, col5
INDEX4 UNIQUE NORMAL col4, pk_col
INDEX5 NONUNIQUE NORMAL col4
There are no Indexes on updated columns.
There are NOT NULL constrains on all updated columns.
Following is my original query.
Could you please suggest something to improve performance.
UPDATE
(
SELECT /*+ INDEX_COMBINE (a,INDEX1)*/
a.col1,
a.col2,
a.col3,
--
CASE val1
WHEN 1 THEN
CASE
WHEN user_func1(paramerets) IS NULL THEN 1000
ELSE 0
END
ELSE 0
END
+
CASE val2
WHEN 1 THEN
CASE
WHEN user_func2(paramerets) = 1 THEN 2000
ELSE 0
END
ELSE 0
END
+
CASE val3
WHEN 1 THEN
CASE
WHEN user_func3(paramerets) IS NULL THEN 3000
ELSE 0
END
ELSE 0
END
+
CASE val4
WHEN 1 THEN
CASE
WHEN user_func4(paramerets)=1 THEN 4000
ELSE 0
END
ELSE 0
END
col1_new,
--
CASE user_pkg.func5 (paramerets)
WHEN 0 THEN
CASE val1
WHEN 2 THEN
CASE
WHEN user_func1(paramerets) IS NULL THEN 1000
ELSE 0
END
ELSE 0
END
ELSE 0
END
+
CASE user_pkg.func5 (paramerets)
WHEN 0 THEN
CASE val2
WHEN 2 THEN
CASE
WHEN user_func2(paramerets) =1 THEN 2000
ELSE 0
END
ELSE 0
END
ELSE 0
END
+
CASE user_pkg.func5 (paramerets)
WHEN 0 THEN
CASE val3
WHEN 2 THEN
CASE
WHEN user_func3(paramerets) IS NULL THEN 3000
ELSE 0
END
ELSE 0
END
ELSE 0
END
+
CASE user_pkg.func5 (paramerets)
WHEN 0 THEN
CASE val4
WHEN 2 THEN
CASE
WHEN user_func4(paramerets) = 1 THEN 4000
ELSE 0
END
ELSE 0
END
ELSE 0
END
col2_new,
--
CASE val1
WHEN 3 THEN
CASE
WHEN user_func1(paramerets) IS NULL THEN 1000
ELSE 0
END
ELSE 0
END
+
CASE val2
WHEN 3 THEN
CASE
WHEN user_func2(paramerets) = 1 THEN 2000
ELSE 0
END
ELSE 0
END
+
CASE val3
WHEN 3 THEN
CASE
WHEN user_func3(paramerets) IS NULL THEN 3000
ELSE 0
END
ELSE 0
END
+
CASE val4
WHEN 3 THEN
CASE
WHEN user_func4(paramerets)=1 THEN 4000
ELSE 0
END
ELSE 0
END
col3_new
from A
where a.col4 = 10 -- list Partitions based on this column -- 12 Distinct values
and a.col5 =
and a.col6_date between to_date('04012009', 'mmddyyyy' ) and to_date('06302009', 'mmddyyyy' )
and a.col7 in (1, 2, 3) -- 4 Distinct values
AND a.col8 IN ('N', 'Y') -- 2 Distinct values
AND a.col9 IS NOT NULL
AND a.col6_date IS NOT NULL
and ( 10 = 10 or a.col10 = 10 ) -- 2 Distinct values
)
zz
--
SET zz.col1 = zz.col1 + zz.col1_new,
zz.col2 = zz.col2 + zz.col2_new,
zz.col3 = zz.col3 + zz.col3_new ;
February 02, 2012 - 8:01 am UTC
Rahul, February 02, 2012 - 8:41 am UTC
Thanks Tom for answering..
I have gone through the article you mentioned above.
It is very helpful.
It seems you are suggesting to use "scalar subquery" in my scenario. Am I right ?
It seems I need to replace all function calls with ( select Fun() from dual ).
In my case, something like below. Right ?
UPDATE
(
SELECT /*+ INDEX_COMBINE (a,INDEX1)*/
a.col1,
a.col2,
a.col3,
--
CASE val1
WHEN 1 THEN
CASE
WHEN ( select user_func1(paramerets) from dual ) IS NULL THEN 1000
ELSE 0
END
ELSE 0
END
+
CASE val2
WHEN 1 THEN
CASE
WHEN ( select user_func2(paramerets) from dual ) = 1 THEN 2000
ELSE 0
END
ELSE 0
END
+
CASE val3
WHEN 1 THEN
CASE
WHEN ( select user_func3(paramerets) from dual ) IS NULL THEN 3000
ELSE 0
END
ELSE 0
END
+
CASE val4
WHEN 1 THEN
CASE
WHEN ( select user_func4(paramerets) from dual ) = 1 THEN 4000
ELSE 0
END
ELSE 0
END
col1_new
........
Do I need to include RESULT_CACHE in function to use "Scalar Subquery" effectively ?
Thanks....
February 02, 2012 - 9:14 am UTC
It seems you are suggesting to use "scalar subquery" in my scenario. Am I right
?yes.
Do I need to include RESULT_CACHE in function to use "Scalar Subquery"
effectively ?
IF you can result_cache it AND result_cache-ing it makes sense
THEN
by all means, result cache it, it won't hurt and may help
END IF
IF the function is deterministic
THEN
by all means, mark it so, it won't hurt and may help
END IF
and then add the scalar subquery on top of it all
you don't HAVE TO, but if you can - it won't hurt you and might help
Rahul, February 02, 2012 - 9:37 am UTC
Thank you very much Tom for your help..
Update a column based on the sort order of another column
Dhruva, February 08, 2012 - 11:04 am UTC
I have an existing table where I want to update a sort order column depending on the alphabetical order of some other column.
DROP TABLE test_table;
CREATE TABLE test_table (
code VARCHAR2(30),
sort_order NUMBER);
INSERT INTO test_table
SELECT object_name, NULL
FROM all_objects
WHERE ROWNUM < 10;
COMMIT;
UPDATE (
SELECT ROWNUM row_num,
code,
sort_order
FROM test_table
ORDER BY code)
SET sort_order = row_num * 10;
Obviously, the update results in error: ORA-01732: data manipulation operation not legal on this view (because of the virtual column).
Using an analytical function also results in the same error:
UPDATE (
SELECT code,
RANK () OVER (ORDER BY code) ra_nk,
sort_order
FROM test_table
ORDER BY code)
SET sort_order = ra_nk * 10;
Is there an elegant way of doing this?
Thanks
February 08, 2012 - 12:18 pm UTC
Obviously, the update results in error: ORA-01732: data manipulation operation not legal on this view (because of the virtual column). not only that - but since rownum is assigned BEFORE sorting, it would result in "garbage" had it worked!
ops$tkyte%ORA11GR2> CREATE TABLE test_table (
2 code VARCHAR2(30),
3 sort_order NUMBER);
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> INSERT INTO test_table
2 SELECT object_name, NULL
3 FROM all_objects
4 WHERE ROWNUM < 10;
9 rows created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> COMMIT;
Commit complete.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> merge into test_table
2 using ( select rowid rid, row_number() over (order by code) rn from test_table ) x
3 on ( test_table.rowid = x.rid )
4 when matched then update set sort_order = x.rn;
9 rows merged.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select * from test_table;
CODE SORT_ORDER
------------------------------ ----------
ICOL$ 3
I_USER1 7
CON$ 1
UNDO$ 9
C_COBJ# 2
I_OBJ# 6
PROXY_ROLE_DATA$ 8
I_IND1 5
I_CDEF2 4
9 rows selected.
SQL
Sanchi, March 03, 2012 - 1:43 am UTC
hello, sir
I want to know that if i want to require data from 2 tables given below but there is same meter no Please send me select query according to this the tables are..
bills:
bill no,
meter no,
previousreading,
currentReading,
amount,
second table is Customer:
custid,
name,
unitrate,
meterno,
reading,
connectiontype,
March 03, 2012 - 8:41 am UTC
huh? no clue what you might mean here.
and I don't see a create table
I don't see any inserts
which means you won't see a select from me.
Make it easy for the person you are asking help from. give them a small sample (but a correct and complete example!). explain in detail what you need.
I want to know that if i want to require data from 2 tables given below but there is same meter no
that doesn't tell us anything - doesn't actually tell us anything.
sumit, July 03, 2012 - 7:43 am UTC
hi tom
I have doute it should count from left
select instr('sumitishappay','p',-1)from dual;
July 03, 2012 - 8:52 am UTC
ops$tkyte%ORA11GR2> rem sumitishappay
ops$tkyte%ORA11GR2> rem 1234567890123
ops$tkyte%ORA11GR2> rem ^ 11th
ops$tkyte%ORA11GR2> select instr('sumitishappay','p',-1)from dual
2 /
INSTR('SUMITISHAPPAY','P',-1)
-----------------------------
11
and indeed it did. It found the last P because it started at the end of the string.
it always returns the position in the string where it found the pattern.
A reader, July 04, 2012 - 7:24 am UTC
Thax..but if i give -1 so it should count from last.
as u told It found the last P because it started at the end of the string.if it started at the of the string so ans should be 3.like substr
select substr('sumitishappay',-7,5)from dual o/p is shapp.
select instr('sumitishappay','p',1)from dual o/p 10
select instr('sumitishappay','p',-1)from dual o/p 11
is it counting from last or not .......
July 05, 2012 - 7:17 am UTC
it always gives the position of the character in the string, it does what it does, not what you think it does.
All software is like that, it does what it does, not what you think it should do based on your interpretation of it.
who is "u"?????
If it starts at the end of the string and looks backs for P, it will find P as POSITION 11 in the string.
If it starts at the end of the string and looks for U, it will find it at POSITION 2 in the string.
It is not COUNTING, it is returning a position in the string of a matching string.
from the documentation:
If a substring that is equal to substring is found, then the function returns an integer indicating the position of the first character of this substring.
No counting, rather "returns a position" - 1, 2, 3, ... N
It is SEARCHING from the last
It then returns the POSITION of the string
will PK FK be messed up?
A reader, July 04, 2012 - 9:44 pm UTC
Hi Tom,
I noticed you mentioned '' in many thread.
My question is whether disable-enable will make the PK and FK mess up?
Of course, when enabling it any error or inconsistent will be reported out.
But whether the time saved by disable it overvalue the time we used to fix the issue during re-enable it?
July 05, 2012 - 8:26 am UTC
''? what is ''?
But whether the time saved by disable it overvalue the time we used to fix the
issue during re-enable it?
I don't know what you mean.
sumit india
A reader, July 07, 2012 - 5:29 am UTC
thx..i get it for that this is a wonderful ex:-
select instr ('abcabcabc','c',1) from dual; o/p-3
SELECT INSTR ('abcabcabc','c',1,2) FROM DUAL; o/p-6
SELECT INSTR ('abcabcabc','c',1,2) FROM DUAL; o/p-9
SELECT INSTR ('abcabcabc','c',-1,2) FROM DUAL; o/p-6
select instr ('abcabcabc','c',-1,1) from dual; o/p-9
sambit baliarsingh, November 14, 2012 - 1:12 am UTC
hi tom i have a question on the update .following is my scenario
i have created a table calles EMP1 with the same structure as EMP
then i updated the emp1 table setting the sal=sal+1000 and comm=comm+1000
now i again want to update the emp1 sal column with the emp table i wrote the following query
update (select a.sal ,b.sal from emp1 a,emp b where a.empno=b.empno)
set a.sal=b.sal
but while i am trying to execute the query its showing
b.sal is invalid identifier.
please help me because its one of the scenario with which i am working .
November 14, 2012 - 8:26 pm UTC
correlation names don't go "up", then can only go "down" a level
select a.sal a_sal, b.sal b_sal from ....
and use a_sal, b_sal in the outer part of the query.
James, November 15, 2012 - 4:34 am UTC
Hi Tom,
I've check all the reviews on this question, but I can't seems to find the right answer on the issue that I've figuring out.
Here is the sample_table1
ID varchar2(5)
BRAND varchar2(2)
PLAN varchar2(10)
DURATION number(4)
REASON varchar2(15)
insert into sample_table1
(101,G,Plan100,20,nonbillable)
(101,G,Plan100,15,nonbillable-unli)
(102,IN,Plan200,30,nonbillable)
(103,IN,Plan200,60,nonbillable)
(104,G,Plan300,10,nonbillable-unli)
(104,G,Plan300,20,nonbillable)
(105,IN,Plan300,5,nonbillable-unli)
lets assume we have those data inside the sample_table1
and here is the
sample_table2
BRAND varchar2(2)
PLAN varchar2(10)
R1_TOT_AVAIL number(5) null
R1_TOT_DURATION number (5) null
R2_TOT_AVAIL number(5) null
R2_TOT_DURATION number (5) null
insert into sample_table2
(G,Plan100,null,null,null,null)
(IN,Plan200,null,null,null,null)
(G,Plan300,null,null,null,null)
(IN,Plan300,null,null,null,null)
lets assume we have those data inside the sample_table2
null values on this table will have to be updated
this was my approach:
UPDATE sample_table2 a
SET R1_TOT_AVAIL = (select count(b.plan) from sample_table1 b where b.plan = a.plan and b.brand = a.brand and trim(b.reason) = 'nonbillable'
group by b.plan, b.brand)
R1_TOT_DURATION = (select sum(b.duration) from sample_table1 b where b.plan = a.plan and b.brand = a.brand and trim(b.reason) = 'nonbillable'
group by b.plan, b.brand)
R2_TOT_AVAIL = (select count(b.plan) from sample_table1 b where b.plan = a.plan and b.brand = a.brand and trim(b.reason) = 'nonbillable-unli'
group by b.plan, b.brand)
R2_TOT_DURATION = (select sum(b.duration) from sample_table1 b where b.plan = a.plan and b.brand = a.brand and trim(b.reason) = 'nonbillable-unli'
group by b.plan, b.brand)
WHERE R1_TOT_AVAIL is null
AND R1_TOT_DURATION is null
AND R2_TOT_AVAIL is null
AND R2_TOT_DURATION is nullQuestion:
1. Would there be a better approach to update those null columns?
2. Can I ask for suggestion on how I can update those columns in the fastest and most efficient way?
Thank you very much Tom! :)
Will be waiting for you response.
November 19, 2012 - 9:21 am UTC
funny, I get all kinds of errors when I try to put your schema in place? the create tables don't actually create anything and the inserts don't actually work???
in the future post working creates and inserts if you want working SQL...
I would use merge in order to avoid indexes if the amount of data to be updated was more than a small number of rows...
ops$tkyte%ORA11GR2> merge into
2 (select *
3 from t2
4 where r1_tot_avail is null
5 and r1_tot_duration is null
6 and r2_tot_avail is null
7 and r2_tot_duration is null ) new_t2
8 using
9 (select plan, brand,
10 count( case when reason = 'nonbillable' then plan end ) new_r1_tot_avail,
11 sum ( case when reason = 'nonbillable' then duration end ) new_r1_tot_duration,
12 count( case when reason = 'nonbillable-unli' then plan end ) new_r2_tot_avail,
13 sum ( case when reason = 'nonbillable-unli' then duration end ) new_r2_tot_duration
14 from t1
15 group by plan, brand ) new_t1
16 on (new_t2.brand = new_t1.brand and new_t2.plan = new_t1.plan)
17 when matched
18 then update set r1_tot_avail = new_r1_tot_avail,
19 r1_tot_duration = new_r1_tot_duration,
20 r2_tot_avail = new_r2_tot_avail,
21 r2_tot_duration = new_r2_tot_duration
22 /
3 rows merged.
updating
A reader, December 11, 2012 - 9:25 am UTC
Performing update based on other table. if there is record then update with its value if no value found then use some column of table being upating. Following query update by values from other table but does not use value of updating column. The values for dept other than 10 are empty after execution of this query.
CREATE TABLE SCOTT.TEST1
( DEPTNO NUMBER(10,0),
DESC1 VARCHAR2(50 BYTE)
);
CREATE UNIQUE INDEX "SCOTT"."UC_DEPT" ON "SCOTT"."TEST1" ("DEPTNO") ;
Insert into TEST1 (DEPTNO,DESC1) values (10,'K1');
alter table emp add(dept_desc varchar2(100), desc1 varchar2(100))
update emp set (dept_desc,desc1)=
(select NVL2(test1.desc1,test1.desc1,SUBSTR(ENAME,1,5)),NVL2(test1.desc1,test1.desc1,SUBSTR(job,1,5)) from test1 where emp.deptno=test1.deptno(+))
but if execute query then it returns proper result and it does not update properly when using for update.
select NVL2(test1.desc1,test1.desc1,SUBSTR(ENAME,1,5)),NVL2(test1.desc1,test1.desc1,SUBSTR(job,1,5)) from test1 ,emp where emp.deptno=test1.deptno(+)
December 17, 2012 - 3:03 pm UTC
... if there is record then update with its
value if no value found then use some column of table being upating. ...
sorry, compilation failed, syntax error line 1 - no idea what you mean by that statement.
I think - based on your failed code snippet - that you wanted to
update emp and set it equal to test1.desc1 if there was a matching record in test1, otherwise - default it to ename/job in the emp table.
outer joins don't work the way you think - your update "select" portion is just like:
select ... from t where t.column = 5(+);
you need to outer join to a "set" - an outer join of A to B returns every row in B at least once. An outer join of A to (nothing) might return nothing.
merge into emp
using (select emp.deptno,
NVL2(test1.desc1,test1.desc1,SUBSTR(ENAME,1,5)) d1,
NVL2(test1.desc1,test1.desc1,SUBSTR(job,1,5)) d2
from test1 ,emp
where emp.deptno=test1.deptno(+)) YOUR_QUERY
on (emp.deptno = your_query.deptno)
when matched then update set dept_desc = d1, desc1 = d2;
A reader, December 18, 2012 - 9:58 am UTC
the merge might fail if there are multiple records for a given deptno in emp, even though the value for desc1 will be same per deptno as there is unique index define on test1.deptno.
December 18, 2012 - 1:21 pm UTC
this is why I should just never ever answer anything that doesn't come with create tables, inserts (test data) and all. anytime I don't actually test it - it doesn't work (imagine that!)
merge into emp
using (select emp.rowid rid,
NVL2(test1.desc1,test1.desc1,SUBSTR(ENAME,1,5)) d1,
NVL2(test1.desc1,test1.desc1,SUBSTR(job,1,5)) d2
from test1 ,emp
where emp.deptno=test1.deptno(+)) YOUR_QUERY
on (emp.rowid = your_query.rid)
when matched then update set dept_desc = d1, desc1 = d2;
thanks!!!!!
of course we have to update by the primary key (or rowid) of EMP.
A reader, December 18, 2012 - 1:41 pm UTC
Sorry Tom I know thats is not sufficient information from OP I am just pointing out that it might fail.
I agree with you when not given enough information only thing left is wild guess :)
table problem
maujood, February 25, 2013 - 3:16 am UTC
i mmaujood ali khan
oracle 10g installed and started working
when i create a table and then inserting the value to this table its not showing the table column in properway.
please tell me the solution
pls help me
shakthi, May 29, 2013 - 12:24 am UTC
i am having 2 tables
1.student_master
regno papercode name m1 m2 m3 total result
2. student_arrear
regno papercode name m1 m2 m3 total result
i need to update student_master from student_arrear those who have passed i have to match the regno and papercode.pls send sql query for this
May 29, 2013 - 7:06 pm UTC
no create
no inserts
no look
even more, no explanation of what "who have passed" - what defines "who have passed". I see a regno (no explanation of what that is), papercode (same comment), name (i guess a student name???) m1, m2, m3 ??????, total (what is total???) result (same here????)
no clue what you need to do.
dynamic cursor
Tony Fernandez, June 05, 2013 - 2:30 pm UTC
Dear Tom,
I have to go through about 200 tables and merge into same tables but with new version.
So table1 will be merged into table1_new, but would like to write generic procedure/package for all 200 at once and not 200 procedures.
The challenge is that when doing the update portion of merge, as in update table1_new set row = orig_row, orig_row needs to be defined as %rowtype of the table in the loop for 200 tables. I was able to define a generic cursor with ref cursor, but can't define a generic %rowtype off of that cursor.
Is there a way to do this or perhaps a better approach?
Your reply is appreciated.
June 05, 2013 - 2:35 pm UTC
write one bit of code to generate the other bits of code.
don't write 200 procedures.
write one procedure that will write 200 procedures. Use a naming convention so it would be easy to read these out - dynamically execute them - and then drop them later.
dynamic cursor
Tony Fernandez, June 05, 2013 - 2:32 pm UTC
Tom,
In my post above, I am not including any code or pseudo code due to the fact that the organization I am working on filters any outgoing "code" thru email or it will be stopped.
Thanks,
June 05, 2013 - 2:36 pm UTC
this isn't "email", you can type code in here, just make it sanitized code
dynamic cursor
Tony Fernandez, June 06, 2013 - 2:50 pm UTC
Nah,
in definition portion we have
c0 ref cursor;
c1 c0;
then somewhere I need to define a variable that holds the rowtype for that unknown yet ref cursor.
That c1 cursor will only know its structure when the cursor is opened with open c1 with sql_stmt.
I hope this makes sense.
Regards,
June 06, 2013 - 3:29 pm UTC
you'll be using dbms_sql then, not a ref cursor. ref cursors have to be known at COMPILE time, you'd need the structure to be known when you COMPILE.
with dbms_sql, you'll have one procedure that can process all 200 tables, it can describe what the cursor returns, it can bind to what the cursor returns and it can fetch an arbitrary row with an unknown (at compile time) set of columns.
and you *can* do it the way I said.
You have 200 tables.
loop over them in the dictionary, generate code for them.
then run said code
Don't use a cursor
David P, June 07, 2013 - 12:25 am UTC
Tony Fernandez, if you are merging data into another table, don't use a cursor at all - If at all possible, query user_tab_columns to build a native dynamic MERGE statement to do the data move. It will be vastly faster and more understandable.
pradeep, June 13, 2013 - 2:26 pm UTC
i have two tables names DETAILS and STATUS
DETALIS contains eid,ename,sal and STATUS contains
eid,sal,updationdate.
now if i want to perform update operation on DETAILS table i need get information on MESSAGE table. how can i write the querry please help me
thanks regard pradeep
June 18, 2013 - 3:20 pm UTC
no create
no inserts
no look
(seems to be light on details too - like what you mean exactly - you talk about details and status but then all of a sudden message pops up????)
re: pradeep
Greg, June 19, 2013 - 4:09 pm UTC
Update table from another table
mkh, September 24, 2013 - 4:45 pm UTC
Hi Tom,
There are 2 tables
Table1
type cpty amt1 amt2
c a 10 0
c a 20 0
nc a 30 0
nc a 40 0
c b 50 0
c b 60 0
nc b 70 0
nc b 80 0
Table2
amtA cpty
100 a
115 a
120 a
125 b
130 b
140 b
Trying to write an single update statement that will do the following
1. Only nc type amt2 column in table1 should be updated
2. amt2 = sum(amtA) from table2 joined by cpty * amt1 / sum(amt1)(cpty wise)
sample output
type cpty amt1 amt2
c a 10 0(type c no update)
c a 20 0(type c no update)
nc a 30 30*(100+115+120) / (30+40)
nc a 40 40*(100+115+120) / (30+40)
c b 50 0 (type c no update)
c b 60 0(type c no update)
nc b 70 70*(125+130+140)/(70+80)
nc b 80 80*(125+130+140)/(70+80)
Plsql or SQL
mkh, October 07, 2013 - 4:16 pm UTC
I managed to make it in plsql, but am eager to find if it is possible to be done under a single update statement