Update statement and Null-values -- update a table from another table
Greg, October 12, 2001 - 12:54 pm UTC
Great help. Straightforward, to the point, gives options for differeing scenarios.
But can Multiple columns be achived
pawan, April 02, 2002 - 12:18 pm UTC
Tom,
I am failing when trying to do the following:
I created two tables t and t1 by select * from user_objects. Both t and t1 have 117 records. Then I took one object_name(PRIMARY_SALESFORE) in t and updated three fields - object_type,object_id and created.
ADHOC@VOYAGER> select t.object_id,t1.object_id,t.object_type,t1.object_type,
2 t.created,t1.created
3 from t,t1
4 where t1.object_name=t.object_name
5 and t.object_name='PRIMARY_SALESFORCE';
OBJECT_ID OBJECT_ID OBJECT_TYPE OBJECT_TYPE CREATED CREATED
--------- --------- ------------- ------------- --------- ---------
2222 17927 ORATAB TABLE 02-APR-02 01-APR-02
Now when I try to update these three fields in t from t1 I am getting the following error. What am I doing wrong.
1 update
2 (select t.object_id,t1.object_id,t.object_type,t1.object_type,
3 t.created,t1.created
4 from t,t1
5 where t1.object_name=t.object_name
6 and t.object_name='PRIMARY_SALESFORCE')
7 set t.object_id=t1.object_id,
8 t.object_type=t1.object_type,
9* t.created=t1.created
ADHOC@VOYAGER> /
update
*
ERROR at line 1:
ORA-00904: invalid column name
WHEN I TRIED TO UPDATE JUST one column in T I get error too
ADHOC@VOYAGER> update
2 (select t.object_id toid,t1.object_id t1oid
3 from t,t1
4 where t.object_name=t1.object_name
5 and upper(t.object_name)='PRIMARY_SALESFORCE')
6 set toid=t1oid;
update
*
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table
April 02, 2002 - 1:42 pm UTC
T and T1 are not "visible" outside of the inline view. You fixed that with the alias names toid, t1oid.
For the second error, please reread the above answer. You NEED a primary key/unique constraint on object_id in T1 to ensure that each row in T joins to AT MOST 1 row in T1.
Consider, if T had:
OBJECT_NAME OBJECT_ID
-------------- ----------------
ABC xxxx
and T2 had:
OBJECT_NAME OBJECT_ID
---------------- ------------------
ABC 123
ABC 456
what should be the outcome of:
ADHOC@VOYAGER> update
2 (select t.object_id toid,t1.object_id t1oid
3 from t,t1
4 where t.object_name=t1.object_name
5 and upper(t.object_name)='PRIMARY_SALESFORCE')
6 set toid=t1oid;
should T have 123 or 456 and under what circumstances? Since the outcome of that update is 100% AMBIGOUS, we do not permit it. We need to ensure key preserved-ness and do that with a primary key/unique constraint.
You need that index anyway typically for performance.
Thanks But ..
pawan, April 02, 2002 - 2:51 pm UTC
Thanks Tom. I knew if I read your answer (one more time) throughly I would be able to find the error in my SQL. But ... Anyway, would you be kind enough to guide me in achieving what I am trying - updating three columns in a table from another table that has one column (object_name) common and unique but no Primary key.
Thanks again for the help
April 02, 2002 - 3:16 pm UTC
update t
set ( f1, f2, f3 ) =
( select a, b, c from t2 where t2.some_field = t.some_field )
where exists ( select a, b, c from t2 where t2.some_field = t.some_field )
/
will do that. If you cannot put a unique constraint (which is NEEDED in concept if not in practice, if the some_field field in t2 is NOT unique -- the above will fail with "subquery returned more then one row" as the update is AMBIGOUS)
ORACLE OPTIMIZER
Prashant, April 02, 2002 - 4:39 pm UTC
I retireve data from WSI_OPPORTUNITY partitioned table. Which is partioned based on column RESP_DATE_CREATED column.
It has two partitioned index. 1st on RESP_DATE_CREATED column and 2nd on CUST_ID column. I have following three queries.
Query 1 : select * from swbapps.wsi_opportunity where cust_id = 640354 and resp_date_created between sysdate-10000 and sysdate
Cost : 202 Execution Time: 1.1 Sec Index Used: CUST_ID
Query 2: select * from swbapps.wsi_opportunity where cust_id = 640354 order by resp_date_created desc
Cost: 1040 Query Execution Time:0.64Sec Index Used: CUST_ID
Query3: select * from swbapps.wsi_opportunity where cust_id = 640354 and resp_date_created between sysdate-10000 and sysdate order by resp_date_created desc
Cost: 177 Query Execution Time: DID NOT RETURN ANY RESULT AFTER 45 MINUTES Index Used: RESP_DATE_CREATED
My question is why query3 is using RESP_DATE_CREATED
column index and forever it hangs? We are using ORACLE 8.1.5 on SUN SOLARIS platform. It is really strange the way oracle optimizer behaves.
Any insight is appreciated.
April 02, 2002 - 11:30 pm UTC
As this has absolutely nothing to do with the original question.... One would need more information (and this is not the appropriate place to get that) such as the CREATE tables/indexes/init.ora settings, etc (please don't add them here -- thats a hint for when you can ask a question -- I'll be asking for that)
Is this a good approach
Anant, April 09, 2002 - 3:20 pm UTC
Thanks for the excellent site. I am trying to update a table based on certain conditions on the same table. The query looks like
DECLARE
CURSOR c1 IS SELECT ROWID FROM
siebel.s_synd_data
where
WHERE
ROW_ID LIKE 'DDM%'
AND DATA_SRC_CD = 'DECPRF'
and POSITION_ID IS NULL;
begin
for cur in c1 loop
UPDATE SIEBEL.S_SYND_DATA A
SET A.POSITION_ID = (SELECT B.ROW_ID FROM SIEBEL.S_POSTN B,
SIEBEL.S_ASGN_GRP C
WHERE A.TERR_ID = C.ROW_ID
AND SUBSTR(C.NAME, 1, 5) || '00D' = B.NAME)
where rowid=cur.rowid;
if mod(c1%rowcount,50000)=0 then
commit;
end if;
end loop;
commit;
end ;
------
The WHERE part of cursor C1 is expected to return 9 million records. My DBA says that the transaction wil commit after updaing 9 million records. I have commit after every 50,000 records - Is it true?
Also what can be done to make this go faster - I know you hate this word withou any performance metrics but what if I create some index (function based)?
Warm regards
April 09, 2002 - 4:01 pm UTC
If this was up to me -- this is what I would do:
update s_synd_data a -- sorry, i cannot bring myself to type sie....
set a.position_id = ( select b.row_id
from s_postn b, s_asng_grp c
where c.row_id = a.terr_id
and b.name = substr(c.name,1,5) || '00D' )
where row_id like 'DDM%'
and data_src_cd = 'DECPRF'
and position_id is null;
if position_id was indexed, I would drop the index, do the update, rebuild the index unrecoverable and in parallel.
before you say "but it's 9million rows, we must commit" -- I say -- no way. Just make sure you have sufficient RBS configured (else you are an ORA-01555 just WAITING to happen with your logic -- search this site for 01555 or 1555 to see what i mean).
It'll not take as much rbs as you think. Procedural code such as your will
a) run slower then an update
b) generate more redo and undo then a single update
c) be error prone (ora-01555, other logic errors)
d) you already have a logic error, c1%rowcount isn't "cumulative", its the number
of rows updated by the last update. Unless you actually update 50,000 rows
in one update, you'll never commit (you have a bug)
e) be a really bad idea.
On my system, I just updated 1,099,008 rows:
ops$tkyte@ORA817DEV.US.ORACLE.COM> update big_table set owner = lower(owner);
Elapsed: 00:08:38.66
ops$tkyte@ORA817DEV.US.ORACLE.COM> select used_ublk from v$transaction;
USED_UBLK
----------
13002
Elapsed: 00:00:00.01
ops$tkyte@ORA817DEV.US.ORACLE.COM> select 13002*8/1024 from dual;
13002*8/1024
------------
101.578125
Elapsed: 00:00:00.01
ops$tkyte@ORA817DEV.US.ORACLE.COM> select count(*) from big_table;
COUNT(*)
----------
1099008
Elapsed: 00:00:02.41
it took about 100meg of rollback. You would be looking at a gig of rollback or so. What I would do is (with my dba) make sure I had that much, use set transaction use rollback segment if needed (just create a temporary one for this update in /tmp or something, drop it later). Run the update. Be done with it.
Less thinking, Less work, Less chance for error...
Update statement
Bhavesh Tailor, April 17, 2002 - 3:30 pm UTC
This is very useful example.
Thanks a lot, Tom
Why am I getting this
pawan, April 29, 2002 - 4:07 pm UTC
Tom,
I do a simple insert into one Synonym while selecting from other synonym ( about 3 million records).
insert into voy_call_prev_sy (
2 select * from voy_call_sy);
insert into voy_call_prev_sy (
*
ERROR at line 1:
ORA-01562: failed to extend rollback segment number 3
ORA-01650: unable to extend rollback segment R02 by 1280 in tablespace RBS02
I have two Rollback segments R01 and R02 both 2 GB.
April 29, 2002 - 9:01 pm UTC
You are generating more then 2gig of rollback (indexes can generate a TON of undo information).
If you have 2gig allocated and no one else is using it and you get this, you are generating more then 2gig.
Thanks
Pawan, April 30, 2002 - 8:05 am UTC
Tom,
No one was using the system when I ran the INSERT . Yes there were indexes on both the tables. But once the database was bounced th inserts went without problem. Do you think that there was some other problem?
Thanks
April 30, 2002 - 11:28 am UTC
I think there was another user or two on the system personally. They may not have been active but they would be enough to jam the RBS if they started a transaction and left it there.
Absolutely fabulous
Dan Kefford, April 30, 2002 - 12:48 pm UTC
Dear Tom...
In what version of Oracle did the "update a join" syntax become available? I have always used the second construction, using the EXISTS clause, to accomplish this kind of thing. It always did seem kind of silly to have to write the same exact subquery twice. ;)
You ought to write an Oracle SQL tricks book.
April 30, 2002 - 1:54 pm UTC
believe it is 7.3
ops$tkyte@ORA734.WORLD> create table t1 ( x int primary key, y int );
Table created.
ops$tkyte@ORA734.WORLD> create table t2 ( x int, y int );
Table created.
ops$tkyte@ORA734.WORLD>
ops$tkyte@ORA734.WORLD> insert into t1 values ( 1, 1 );
1 row created.
ops$tkyte@ORA734.WORLD> insert into t1 values ( 2, 2 );
1 row created.
ops$tkyte@ORA734.WORLD>
ops$tkyte@ORA734.WORLD> insert into t2 values ( 2, null );
1 row created.
ops$tkyte@ORA734.WORLD> insert into t2 values ( 3, null );
1 row created.
ops$tkyte@ORA734.WORLD>
ops$tkyte@ORA734.WORLD>
ops$tkyte@ORA734.WORLD> update ( select t1.y t1_y, t2.y t2_y
2 from t1, t2
3 where t1.x = t2.x )
4 set t2_y = t1_y
5 /
1 row updated.
ops$tkyte@ORA734.WORLD> select * from t2;
X Y
---------- ----------
2 2
3
ops$tkyte@ORA734.WORLD>
Very useful!
Cynthia Pinchot, July 15, 2002 - 8:35 pm UTC
A much clearer explanation of what it takes to update
a join view, than what was described in the Oracle manuals.
Parallel dml and update join view
Rob, August 02, 2002 - 11:04 am UTC
Tom:
ADHOC@VOYAGER> update
2 (select t.object_id toid,t1.object_id t1oid
3 from t,t1
4 where t.object_name=t1.object_name
5 and upper(t.object_name)='PRIMARY_SALESFORCE')
6 set toid=t1oid;
In the above query which demonstrates updating a join view,
I was wondering whether this update will execute in parallel if t is partitioned and you issue alter session enable parallel dml. I have a similiar update and I have determined that the best plan to resolve the select stmt would be to FTS both tables and hash join.
Thanks as always
Rob
August 05, 2002 - 11:19 am UTC
If all of the other criteria are met (parallel stuff is enabled, tables are set as "parallel") -- it should be able to.
It would break up the update by partitions on T, full scanning each partition on T and joining it to T1 after down selecting the full scans with the predicate on upper(t.object_name).
One way to find out -- run it, then try to select * from t where rownum = 1; If that succeeds, no parallal update, otherwise if that fails, parallel update happened. Commit and you can select from it.
Vijay Malhotra, August 05, 2002 - 1:58 pm UTC
It's great answer.
Can this be done, if more than two tables involved?
Prince., August 27, 2002 - 5:57 pm UTC
I have been doing this with two tables in the join.
Can this be done, if I want to update the column of table A with Column of Table C. The relation between the tables are A -> B -> C, where A is the parent of all, B is the parent of C.?
August 27, 2002 - 9:27 pm UTC
sigh, such a simple simple example -- you spec'ed it all out -- and yet, you didn't try??? oh well.
the answer is yes, as long as the rules for "key preserved" are satisified -- that is -- each row in A will appear AT MOST ONCE in the resulting join. If not, then no.
Here is the simple example:
ops$tkyte@ORA920.LOCALHOST> create table a ( x int primary key, y int );
Table created.
ops$tkyte@ORA920.LOCALHOST> create table b ( x references a primary key );
Table created.
ops$tkyte@ORA920.LOCALHOST> create table c ( x references b primary key, y int );
Table created.
ops$tkyte@ORA920.LOCALHOST>
ops$tkyte@ORA920.LOCALHOST> insert into a values ( 1, null );
1 row created.
ops$tkyte@ORA920.LOCALHOST> insert into a values ( 2, null );
1 row created.
ops$tkyte@ORA920.LOCALHOST>
ops$tkyte@ORA920.LOCALHOST>
ops$tkyte@ORA920.LOCALHOST> insert into b values ( 1 );
1 row created.
ops$tkyte@ORA920.LOCALHOST>
ops$tkyte@ORA920.LOCALHOST> insert into c values ( 1, 100 );
1 row created.
ops$tkyte@ORA920.LOCALHOST>
ops$tkyte@ORA920.LOCALHOST> select * from a;
X Y
---------- ----------
1
2
ops$tkyte@ORA920.LOCALHOST> update ( select a.y a_y, c.y c_y
2 from a, b, c
3 where a.x = b.x and b.x = c.x )
4 set a_y = c_y;
1 row updated.
ops$tkyte@ORA920.LOCALHOST>
ops$tkyte@ORA920.LOCALHOST> select * from a;
X Y
---------- ----------
1 100
2
ops$tkyte@ORA920.LOCALHOST>
ops$tkyte@ORA920.LOCALHOST>
kalita, August 28, 2002 - 4:55 am UTC
Good one...
WHERE EXISTS qualification in 8.1.7 ?
Ron Chennells, September 30, 2002 - 6:10 am UTC
Tom the original question was for a 7.3.4 datbase.
I'm using 8.1.7.3.
If I choose to use the second method that use suggest.
Do I still need the WHERE EXISTS qualification for this version ?
set columnName = ( select value
from lookup
where lookup.keyname = name.keyname
and otherColumn = :other_value )
where exists ( select value
from lookup
where lookup.keyname = name.keyname
and otherColumn = :other_value )
Thanks
Ron Chennells
September 30, 2002 - 7:29 am UTC
You can either
o update the join as demonstrated
o update via where exists as demonstrated
if you drop the where exists you will update EVERY row in the NAME table, setting columnName to NULL whenever there isn't a value in LOOKUP for that key. The where exists causes us to ONLY UPDATE rows that have a mate in LOOKUP.
I never knew that you could update using Inline view....
A reader, September 30, 2002 - 11:40 am UTC
Tom,
Great stuff you have out there.
My question is on this point which is from one of your replies.
d) you already have a logic error, c1%rowcount isn't "cumulative", its the number of rows updated by the last update. Unless you actually update 50,000 rows in one update, you'll never commit (you have a bug)
I thought only SQL%ROWCOUNT was the one which was set during a transaction statement. So, would <CURSOR>%ROWCOUNT also be changed during a transaction? Please explain further on this.
regards
October 01, 2002 - 9:17 am UTC
whoops- you know what, i was thinking sql%rowcount myself.
c1%rowcount IS cumulative! bummer....
c1%rowcount is maintained -- it is the number of rows fetched so far (i use explicit cursor soooo infrequently, I slipped up on that one)
Love the Update Join....
Mike, October 01, 2002 - 8:41 am UTC
...but I can't get it to work. Every time I try to use it, I get a permissions error (ORA-01031: insufficient privileges). Could that be related to not having unique constraints on my fields?...or do you have to have update permissions on both tables in the join (even if only one table is being updated)? Even though the second, where exists method works fine, I take a serious performance hit as I have to query a very large table twice. I would love to use the update join if possible without having update permissions on the second table. Hope that makes sense...Thanks! (I'm on version 8.1.7.3.0)
October 01, 2002 - 10:02 am UTC
you would need update on all tables in the inline view.
locks
A reader, October 02, 2002 - 11:20 am UTC
Tom,
In case of
update
( select columnName, value
from name, lookup
where name.keyname = lookup.keyname
and lookup.otherColumn = :other_value )
set columnName = value
/
will both tables name, lookup get locked during the update?
October 02, 2002 - 7:27 pm UTC
ops$tkyte@ORA920.LOCALHOST> alter table emp add dname varchar2(30);
Table altered.
ops$tkyte@ORA920.LOCALHOST>
ops$tkyte@ORA920.LOCALHOST> update ( select emp.dname edname, dept.dname ddname
2 from emp, dept
3 where emp.deptno = dept.deptno )
4 set edname = ddname;
14 rows updated.
ops$tkyte@ORA920.LOCALHOST>
ops$tkyte@ORA920.LOCALHOST> select type, id1, (select object_name from user_objects where object_id = id1 ) oname
2 from v$lock
3 where sid = (select sid from v$mystat where rownum=1)
4 and type = 'TM'
5 /
TY ID1 ONAME
-- ---------- --------------------
TM 31715 EMP
Just the table you update has any row locks placed on it
update
mo, October 22, 2002 - 6:42 pm UTC
Tom:
I ran your query trying to update multiple records from one table to another but did not work.
here is the example:
INS> select * from test;
SSN NAME DOB
---------- ------------------------------ ---------
777 Joe Blow 01-JAN-02
456 Henry walkman 17-JAN-02
444 Tony walkman 18-JAN-02
555 Karin wilsow
888 john kare 01-MAR-02
3333 Cindy Best 04-MAR-02
222 Joe Blow 24-APR-02
3444 Joe Blow 24-APR-02
0000 24-APR-02
611 John 18-OCT-02
10 rows selected.
INS> select * from test2;
SSN PANTS
---------- ----------
611 RED
611 BLUE
456 YELLOW
3 rows selected.
INS> update test
set pants = (select pants from test2 where test.ssn = test2.ssn)
where exists (select pants from test2 where test.ssn = test2.ssn);
set pants = (select pants from test2 where test.ssn = test2.ssn)
*
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row
Is this query limited to one row only. Can I do it for all records in test.
2. Is it true that you can do anything in SQL that PL/SQL can do. Any example that contradicts this?
Thank you,
October 22, 2002 - 7:20 pm UTC
1) you are missing the point of the error.
It is saying the SUBQUERY
set pants = (select pants from test2 where test.ssn = test2.ssn)
returns more then one row. Tell me -- should pants be set to RED or BLUE for 611??? Your subquery MUST return at MOST one row. If it returns two, we have no idea which of the two to use.
2) No idea where you got that idea. SQL is a non-procedural language. PLSQL is a procedural language. They are totally different.
query
mo, October 22, 2002 - 8:34 pm UTC
Tom:
Yes I realized that later. So the queries you suggest will work for tables that have one-to-one record using a primary key.
SQL> update
( select a.pants, b.pants
from test, test2
where test.ssn = test2.ssn)
set pants = pants
or
SQL> update test
set pants = (select pants from test2 where test.ssn = test2.ssn)
where exists (select pants from test2 where test.ssn = test2.ssn);
set pants = (select pants from test2 where test.ssn = test2.ssn)
After deleting the child record for 611.
Correct?
ALso, if you had one record that has multiples in the other tables would it stop and not do antyhing or it will do it for all others except one?
Thank you
October 22, 2002 - 9:15 pm UTC
correct
do not understand last part after "ALso..."
query
mo, October 23, 2002 - 9:20 am UTC
Tom:
why this one fails:
1 update
2 ( select a.pants, b.pants
3 from test a, test2 b
4 where a.ssn = b.ssn)
5* set a.pants = b.pants
SQL>
SQL> /
set a.pants = b.pants
*
ERROR at line 5:
ORA-00904: invalid column name
The first method worked though. The column is there.
2. What I mean as the difference between SQL and PLSQL is that is not there cases (update) where you have to do PLSQL and LOOPS and you can not do it with SQL alone?
THank you
October 23, 2002 - 9:44 am UTC
read the comment "But can Multiple columns be achived " again. Its the answer.
2) of course. SQL is not a procedural lanaguage. PLSQL is. of course there are things you can do in PLSQL that you cannot do in sql.
Update statement and Null-values -- update a table from another table
Rich, November 13, 2002 - 3:24 pm UTC
Thanks Tom. This worked well for for me.
Update a table from another table
Michael Johnson, January 11, 2003 - 11:50 am UTC
Excellent! Tom does it yet again!
That's my ORA-00904 error taken care of! Not entirely sure why I was getting it in the first place. Would anyone like to shed some light?
update pcdb_User_am
set pcdb_User_am.PCUS_TITLE = pcdb_user_c.PCUS_TITLE
where pcdb_User_am.PCUS_USER_RN = pcdb_User_c.PCUS_USER_RN
Returns ORA-00904 error
I would just like to say how much this site is helping an Oracle novice such as my good self!
The book ain't half bad either!
Thanks again!
January 11, 2003 - 12:35 pm UTC
the 904 happened because the above query quite simply doesn't reference PCDB_USER_C anywhere in a from clause. It is as if that table didn't exist
you need to reference the table at some point and you didn't. You tried to use it, without "declaring" it if you will.
udpate statement
Jack, January 29, 2003 - 7:27 pm UTC
scott@ORA734.WORLD> update
2 ( select columnName, value
3 from name, lookup
4 where name.keyname = lookup.keyname
5 and lookup.otherColumn = :other_value )
6 set columnName = value
7 /
1 row updated.
scott@ORA734.WORLD> update name
2 set columnName = ( select value
3 from lookup
4 where lookup.keyname = name.keyname
5 and otherColumn = :other_value )
6 where exists ( select value
7 from lookup
8 where lookup.keyname = name.keyname
9 and otherColumn = :other_value )
10 /
1 row updated.
Tom ,
of the above two which one is better performance wise?
I'm guessing the first one, still have a doubt. If not pl. explain me why?
January 30, 2003 - 8:41 am UTC
neither,
the first one
no wait, the second
I think it is both.....
they will perform differently based on the optimizer you use, the validity of the statistics, and so on.
In general, if "NAME" was very very small and "LOOKUP" was very very large (yet indexed on keyname/othercolumn/value) -- the where exists would be very safe.
The update of a join is generally the choice you want to go with.
Update Statement
Jack, January 29, 2003 - 7:58 pm UTC
Tom,
I found the answer on your site itself. Sorry for the earlier mail.
After implementing this in my code, I 'm getting the following error
ERROR at line 61:
ORA-01779: cannot modify a column which maps to a non key-preserved table.
I read some of your suggestions to have a pk.
In my query , I use this for batch update which cannot have a condition like pk = XXX.
I look for some status in the table and want to update all matching status conditions.
Is there any other efficient way of doing this.
(as in sub queries with set and in where clause, I have to join lot of tables say eg:10 and with same conditions).
thanks again
January 30, 2003 - 8:42 am UTC
got an example schema (creates) and data (inserts). make it small and we can tell you what to do with that.
Update Master from a Detail Table (detail has composite key)
saj, January 30, 2003 - 1:33 pm UTC
Thanks for all the guidance!
Can you guide me in achieving what I am trying
Updating a master table 2 or more columns
from a child table that has one has a composite key as the primary key
example
Table: Food
(food_id number primary key
food_name varchar2(100)
calorie number
fibre number
total_fat number
)
Sample data for Food
Food_id food_name calorie fibre total_fat
1 apple null null null
2 beer null null null
Table: Food_Portion
(food_id number
portion_id number
qty number
weight number
calorie number
fibre number
total_fat number
) where food_id + portion_id is primary key
Sample data for Food_Portion
Food_id Portion_ID qty weight calorie fibre total_fat
1 1 1 10 10 3 3
1 2 2 15 20 5 2
1 3 100 100 60 10 9
2 1 1 10 5 0 0
2 2 .5 6 4 0 0
2 3 100 100 40 0 10
The update should set the calorie, fibre and total_fat in food_table to the values in food_portion table where the food_portion.qty = 100 and food_portion.weight = 100
There rule is there will be only one row per food_id in the food_portion table with qty = 100 and weight = 100 ( that is, select food_id from food_portion where qty = 100 and weight = 100 will give exactly one row per food_id)
I ran this type of update
update ( select a1, b1 from a, b where a.id = b.id )
set a1 = b1 (this one I havenÂ’t tried)
But It gave me the ‘ORA-01779: cannot modify a column which maps to a non key-preserved table ‘ error
Then I tried the
update t
set ( f1, f2, f3 ) =
( select a, b, c from t2 where t2.some_field = t.some_field )
where exists ( select a, b, c from t2 where t2.some_field = t.some_field )
update a
set a1 = ( select b1 from b where b.id = a.id )
where a.id in ( select id from b )
both of them gave me the
ORA-01427: single-row subquery returns more than one row
Error
My question isÂ…
what is my option other than PL/SQL for this kind of update?
January 30, 2003 - 1:48 pm UTC
If you have:
where t2.some_field = t.some_field )
and you get:
ORA-01427: single-row subquery returns more than one row
Error
then your basic premise that there is only one row/food_id is false.
but anyway, if in fact:
select food_id
from food_portion
where qty = 100 and weight = 100
group by food_id
having count(*) > 1;
returns ZERO rows (no dups!) then
ops$tkyte@ORA817DEV> update food
2 set (calorie,fibre,total_fat) =
3 (select calorie,fibre,total_fat
4 from food_portion fp
5 where fp.food_id = food.food_id
6 and fp.qty = 100
7 and fp.weight = 100 )
8 where exists
9 (select calorie,fibre,total_fat
10 from food_portion fp
11 where fp.food_id = food.food_id
12 and fp.qty = 100
13 and fp.weight = 100 )
14 /
is your update.
Thank You Soooo much
SAJ, January 30, 2003 - 2:35 pm UTC
There was one food item in my database which had more than row with qty = 100 and weight = 100. I fixed that row and the query worked. It was a data issue. I needed to have more where conditions to make the row unique.
Thanks a Million for the prompt response!
update statement performance
Jack, January 31, 2003 - 6:51 pm UTC
Tom,
I have the following table structures with online application. Orders are processed in batch. The * fields are populated through online and rest of the columns are filled for business purpose.I have to perform this update in a batch application.
order table (1 million)
------------
order_id number *
order_qty number *
order_amt number *
customer_id number *
location_id number *
order_payment_method_id varchar2 *
order_status number *
order_date date *
order_process_date date
order_cust_first_name varchar2
order_cust_last_name varchar2
order_payment_method_account_no varchar2,
order_payment_method_type
order_cust_addr1
order_cust_addr2
order_cust_city
order_cust_state
order_cust_zip
order_cust_ct
location_name
customer table (0.75 million)
-------------
customer_id
customer_first_name
customer_last_name
customer_status
customer_address table (2 million)
------------------
customer_id
cust_addr1
cust_addr2
cust_city
cust_state
cust_zip
cust_ct
cust_current_addr varchar2(1)
paymentMethod table (1 million)
-------------------
customer_id number
payment_method_id number
payment_method_type varchar2
payment_method_account_no varchar2
payment_method_status
location table (5000)
----------------
locaiton_id number
location_name, varchar2
location_address varchar2,
..
..
.
update order
set order_status ='PROCESSED'
order_process_date = sysdate
(order_cust_first_name,
order_cust_last_name,
order_payment_method_type,
order_payment_method_account_no,
order_cust_addr1,
order_cust_addr2,
order_cust_city,
order_cust_state,
order_cust_zip,
order_cust_ct,
location_name) = (select customer_first_name,customer_last_name,payment_method_type,payment_method_account_no,
cust_addr1,cust_addr2,cust_city,cust_state,cust_zip,cust_ct,location_name
from customer,customer_address,paymentMethod ,location
where order.customer_id = cust.customer_id
and cust_current_addr='Y'
and customer_address.customer_id = cust.customer_id
and payment_method.payment_method_id = order.payment_method_id
and location.location_id=order.location_id)
where order_status is null
and order_date = trunc(sysdate)
and
AND EXISTS
(
SELECT 1
FROM customer cust, paymentMethod pm
WHERE customer_status = 'ACTIVE'
AND order.customer_id = cust.customer_id
AND payment_method_status= 'ACTIVE'
AND order.pm_id = pm.pm_id
)
This update is taking 20 -30 min. based on the volume of processing. I felt the queries are execute twice , one time in select and one time in where clause.
I want to reduce time on this update.
Pl.suggest me best way of updating this.
a) Is any other way,we can rewrite this query ?
b) Going to pl/sql (bulk collection option) will get I any performance gain. If so, what is recommended value for bulk collection array size.
c) if pl/sql gives me better performance, will this performance decrease over the time with more data?
Note: I can't commit in between the batch(business decision).
pl. suggest the best solution as always.
thanks
Jack
January 31, 2003 - 6:53 pm UTC
care to share a db version
update statement
Jack, February 01, 2003 - 1:13 pm UTC
My db version is 8.1.7.x.
February 01, 2003 - 1:39 pm UTC
well, I was going to play around with it but so many of the column names and such are not right (making your example "non runnable").....
anyway, in 9i, you'll be able to use MERGE to do this in one pass. The one thing I can say is:
o consider IN over exists (try it)
o make sure you are using CBO with current and good stats
o if the number of unique "customer_id, order_payment_method_id, location_id" values being updated is SMALL compared to the number of records (eg: the main subquery is being executed 100,000 times but with only 100 different inputs), you may be better off running the subquery into a global temporary table to get the 100 different rows and then updating a join.
Useful answer (and great website).
Lusiana Lusiana, March 10, 2003 - 12:10 pm UTC
Great, I did not know you could do an update on a join. It definately solved my problem in one single statement. Thanks again for your great job providing this website. It has been a very useful and dependable resource for me.
Very useful example
Chip, March 31, 2003 - 12:05 am UTC
After seeing the update syntax for a join, found it works quite nicely for updating a single data source while previewing before and after values:
scott@MSORA920>create table dept_demo as select * from dept;
Table created.
scott@MSORA920>SELECT deptno, dname, INITCAP(dname) Iname, loc, INITCAP(loc) Iloc FROM dept_demo;
DEPTNO DNAME INAME LOC ILOC
---------- -------------- -------------- ------------- -------------
10 ACCOUNTING Accounting NEW YORK New York
20 RESEARCH Research DALLAS Dallas
30 SALES Sales CHICAGO Chicago
40 OPERATIONS Operations BOSTON Boston
scott@MSORA920>edit
Wrote file afiedt.buf
1 UPDATE(
2 SELECT deptno, dname, INITCAP(dname) Iname, loc, INITCAP(loc) Iloc FROM dept_demo
3* ) SET dname = Iname, loc = Iloc
scott@MSORA920>/
4 rows updated.
scott@MSORA920>edit
Wrote file afiedt.buf
1* SELECT deptno, dname, INITCAP(dname) Iname, loc, INITCAP(loc) Iloc FROM dept_demo
scott@MSORA920>/
DEPTNO DNAME INAME LOC ILOC
---------- -------------- -------------- ------------- -------------
10 Accounting Accounting New York New York
20 Research Research Dallas Dallas
30 Sales Sales Chicago Chicago
40 Operations Operations Boston Boston
scott@MSORA920>
Thanks :)
A reader, May 01, 2003 - 2:41 pm UTC
Tom,
From your original example, if instead of having
Update...
SET columnName = Value;
I have it as:
Update...
SET Value = columnName;
I get "ORA-01779: cannot modify a column which maps to a non key-preserved table" error (please see below).
In the query, doesn't columnName return only 1 record as well? Why should it not work?
SQL> CREATE TABLE name (
2 keyname INT,
3 columnName VARCHAR2(25)
4 );
Table created.
SQL>
SQL> CREATE TABLE lookup (
2 keyname INT PRIMARY KEY,
3 value VARCHAR2(25),
4 otherColumn INT
5 );
Table created.
SQL>
SQL> insert into name values ( 100, 'Original Data' );
1 row created.
SQL> insert into name values ( 200, 'Original Data' );
1 row created.
SQL> insert into lookup values ( 100, 'New Data', 1 );
1 row created.
SQL> commit;
Commit complete.
SQL> UPDATE (
2 SELECT a.columnName, b.value
3 FROM name a, lookup b
4 WHERE a.keyname = b.keyname AND
5 b.otherColumn = 1)
6 SET columnName = value;
1 row updated.
SQL> rollback;
Rollback complete.
SQL> UPDATE (
2 SELECT a.columnName, b.value
3 FROM name a, lookup b
4 WHERE a.keyname = b.keyname AND
5 b.otherColumn = 1)
6 SET value = columnName;
SET value = columnName
*
ERROR at line 6:
ORA-01779: cannot modify a column which maps to a non key-preserved table
May 01, 2003 - 3:24 pm UTC
SQL> CREATE TABLE name (
2 keyname INT,
3 columnName VARCHAR2(25)
4 );
Table created.
SQL>
SQL> CREATE TABLE lookup (
2 keyname INT PRIMARY KEY,
3 value VARCHAR2(25),
4 otherColumn INT
5 );
insert into name values ( 1, 'x' );
insert into name values ( 1, 'y' );
insert into lookup values ( 1, null, null );
now, what would the answer be to :
SQL> UPDATE (
2 SELECT a.columnName, b.value
3 FROM name a, lookup b
4 WHERE a.keyname = b.keyname AND
5 b.otherColumn = 1)
6 SET value = columnName;
should value be x or y?
you need a primary key on the "lookup" tables join column to remove the ambiguity -- you do not have that.
A reader, May 01, 2003 - 3:28 pm UTC
oh, I'm sorry. I din't see the primary key defined for keyname in lookup table. I was wondering why the update went through when updating lookup, but not name. Got it. Thanks.
update table
A reader, May 02, 2003 - 4:22 am UTC
Tom Excellent example !!!
singl-row subquery returns > 1 row
John A Buchanan, May 08, 2003 - 2:24 pm UTC
Tom,
I understand the 2 points in this query
1. return 0 or 1 rows only
2. use WHERE EXISTS to stop update to null
I use max to return a single row, but I still get non-selected rows updated to NULL
update test
set pants = (select pants from test2 where test.ssn = test2.ssn)
where exists (select pants from test2 where test.ssn = test2.ssn);
set pants = (select pants from test2 where test.ssn = test2.ssn)
*
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row
Suppose pants is type DATE:
update test
set pants = (select max(pants) from test2 where test.ssn = test2.ssn)
where pants > '01-JAN-03'
and exists (select max(pants) from test2 where test.ssn = test2.ssn);
I get all records in test with pants > '01-JAN-03' and no match in test2 updated to null.
Does my 'where pants > '01-JAN-03' make the difference
May 09, 2003 - 12:22 pm UTC
two things
MOST IMPORTANT THING YOU'LL LEARN TODAY:
compare strings to strings, dates to dates, numbers to numbers. Never but Never but Never rely on implicit conversions. Always but Always use date formats and to_date to compare a string to a date. AND USE 4 DIGITS to represent what year it is. It takes no more and no less then 4 digits to do that!
where pants > to_date( '01-JAN-2003', 'dd-mon-yyyy')
that is the only correct way to do that.
Second:
select max(foo) from t where 1=0
always returns a row. an aggrete with NO GROUP BY always returns a row. Always.
update test
set pants = (select max(pants)
from test2
where test.ssn = test2.ssn)
where pants > to_date( '01-JAN-2003', 'dd-mon-yyyy' )
and exists (select pants /* want to make sure there is at least ONE */
from test2
where test.ssn = test2.ssn);
Aggregate functions return 1 row
John A Buchanan, May 09, 2003 - 3:12 pm UTC
Lesson learned
Thanks Tom
Super
Kamran, June 12, 2003 - 12:32 am UTC
Great aid for such frequently encountering problem
Clarification sought
Anirudh, June 12, 2003 - 8:30 am UTC
I have a table original(cart_key number primary key,address_key number )
and another table lookup(address_key number primary key,add_type number,cart_key number references originaL).
Due to some basic design flaw, at this point of time we have records going awry in both the tables.
Address_key column in the original table has no entries i.e NULL (though table in itself has about 100 rows).
lookup table has multiple entries for each cart_key with the add_type being different(1,2 or 3) I am required to update table ori from table lookup to fill the address_key values only where the address type is 1.
Another horible thing is in lookup table there may be a cart key which doesn't have add_type 1 corresponding to it.
Here is the model of the situation;
Anna anirudh@TEST.WORLD816>create table original(cart_key number primary key,address_key number );
Table created.
Anna anirudh@TEST.WORLD816>create table lookup(address_key number primary key,add_type number,cart_key number references original);
Table created.
Anna anirudh@TEST.WORLD816>insert into original values(1000,'');
1 row created.
Anna anirudh@TEST.WORLD816>insert into original values(2000,'');
1 row created.
Anna anirudh@TEST.WORLD816>insert into original values(3000,'');
1 row created.
Anna anirudh@TEST.WORLD816>insert into original values(4000,'');
1 row created.
Anna anirudh@TEST.WORLD816>commit;
Commit complete.
Anna anirudh@TEST.WORLD816>insert into lookup values(101,1,1000);
1 row created.
Anna anirudh@TEST.WORLD816>insert into lookup values(102,2,1000);
1 row created.
Anna anirudh@TEST.WORLD816>insert into lookup values(103,3,1000);
1 row created.
Anna anirudh@TEST.WORLD816>insert into lookup values(104,3,2000);
1 row created.
Anna anirudh@TEST.WORLD816>insert into lookup values(105,2,2000);
1 row created.
Anna anirudh@TEST.WORLD816>insert into lookup values(106,1,3000);
1 row created.
Anna anirudh@TEST.WORLD816>insert into lookup values(107,3,4000);
1 row created.
Anna anirudh@TEST.WORLD816>commit;
Commit complete.
Anna anirudh@TEST.WORLD816>select * from original;
cls CART_KEY ADDRESS_KEY
---------- -----------
1000
2000
3000
4000
Anna anirudh@TEST.WORLD816>select * from lookup;
clsADDRESS_KEY ADD_TYPE CART_KEY
----------- ---------- ----------
101 1 1000
102 2 1000
103 3 1000
104 3 2000
105 2 2000
106 1 3000
107 3 4000
7 rows selected.
I want to update table original such that the data in the table is like this:
CART_KEY ADDRESS_KEY
---------- -----------
1000 101
2000
3000 106
4000
Anna anirudh@TEST.WORLD816>ed
Wrote file afiedt.buf
1 update (select a.address_key w,b.address_key z
2 from original a,lookup b
3 where a.cart_key=b.cart_key
4* and b.add_type=1) set w=z
Anna anirudh@TEST.WORLD816>/
and b.add_type=1) set w=z
*
ERROR at line 4:
ORA-01779: cannot modify a column which maps to a non key-preserved table
Should I write a PL/SQL code for it.............
Thanks
June 12, 2003 - 9:22 am UTC
update t1
set (c1,c2,c3...) = ( select c1,c2,c3...
from t2
where t1.key(s) = t2.key(s) )
where exists ( select c1,c2,c3...
from t2
where t1.key(s) = t2.key(s) )
/
great anser TOM
BT, July 08, 2003 - 10:32 am UTC
cannot select ROWID from a join view
A reader, July 29, 2003 - 1:06 am UTC
Tom,
i am getting the following error when selecting rowid. please let me know what i am missing... sorry for my ignorance on this ..
if i want to select rowid from join of tables what should i do and where will oracle take the row id from ?
SQL> select rowid,ORDER_REQUISITION_NUM from V_ORDER_DOCQUOTE2;
select rowid,ORDER_REQUISITION_NUM from V_ORDER_DOCQUOTE2
*
ERROR at line 1:
ORA-01445: cannot select ROWID from a join view without a key-preserved table
create or replace view v_order_docquote3 as
select
a.row_id order_row_id, a.x_om_office_cd||'-'||a.x_base_req_dsc||'-'||a.x_alt_req_dsc order_requisition_num,
a.x_cust_po_val_amt purchase_order_value,
a.x_charge_to_dsc ,
a.x_cust_po_num primary_cust_no,
decode(a.x_cust_po_dt,null,to_date(null),a.x_cust_po_dt) customer_po_date,
--a.ou_num customer_index_number,
a.x_addr_line2_dsc ship_to_address_line2,
a.x_addr_line3_dsc ship_to_address_line3,
a.x_addr_line4_dsc ship_to_address_line4,
a.x_base_req_dsc base_requisition_number,
a.x_serve_off_cd sales_office_taking_cd,
a.x_inst_city_dsc ,
a.x_inst_state_cd location,
a.x_prog_payment_cd billing_type,
a.x_future_5_dsc title_transfer_terms,
a.x_bill_sch_dsc billing_terms,
a.x_dest_site_cd order_ext_customer_index_num,
c.name terms_of_payment,
b.name project_name,
b.quote_num proposal_num,
d.addr ship_to_address_line1,
d.city ship_to_city,
d.state ship_to_state,
d.zipcode ship_to_zip,
d.country ship_to_country
from
s_order a ,s_doc_quote b ,s_payment_term c,s_addr_org d, s_org_ext e
where a.quote_id = b.row_id
and a.accnt_addr_id = d.row_id(+)
and a.payment_term_id = c.row_id(+)
and a.accnt_id = e.row_id(+);
thanks for your time.
July 29, 2003 - 7:04 am UTC
there is no rowid to select. None of the tables are key preserved.
if you want the rowid from a specific table in there - you would ADD that column to the view
select a.rowid a_rowid, ......
and then you can select A_ROWID in the select statement.
key preserved
A reader, July 29, 2003 - 10:52 pm UTC
tom,
"None of the tables are key preserved"
i have not given you table structure, how did you find out that none of my tables are key preserved, please let me know.
Also ...tom, i read through the key preserved postings in your site -but unfortunately i am not understanding the basics behind that. - true that you have give a simple illustration in this too. but i am asking you, if you get chance- can you explain me with the above tables, which i have used for creating the view. not all tables, just two in that would do.
thanks for your contribution to oracle community.
July 30, 2003 - 7:18 am UTC
if there were a key preserved table, rowid would have been there.
the error message suggested as such:
ERROR at line 1:
ORA-01445: cannot select ROWID from a join view without a key-preserved table
basically a key preserved table is a table in a JOIN that is gauranteed to return each of its rows AT MOST once in the query (eg: it will join to at most ONE other row)
So, in
select * from emp, dept where emp.deptno = dept.deptno;
assuming a primary key on dept(deptno) and nothing else we can say that "Emp is key preserved". Why? because each row in EMP will join to AT MOST one row in DEPT -- so the record for KING can appear AT MOST once in the result set. EMP is a "stable" table in that result set.
DEPT however is NOT key preserved. the record for deptno=10 will appear many times in the result set.
Thanks
A reader, July 30, 2003 - 10:39 am UTC
Tom,
Thanks, Nothing can match your clear way of explaining. your dept,emp example helped me understand the basics behind it.
Thanks again ..
;-)
update query
umesh, August 11, 2003 - 8:50 am UTC
Tom,
Can this be done in a query or should i do a pl/sql block for it
Thanks
scott>create table mfg
( mfg_id varchar2(5) primary key ,
step varchar2(5),
component varchar2(5),
parent_id varchar2(5)
);
insert into mfg ( mfg_id,step,component) values ('m1','s1','c1');
insert into mfg ( mfg_id,step,component) values ('m2','s2','c2');
insert into mfg ( mfg_id,step,component) values ('m3','s3','c3');
insert into mfg ( mfg_id,step,component) values ('m4','s4','c4');
create table test
(
STEP VARCHAR2(5),
COMPONENT VARCHAR2(5),
P_STEP VARCHAR2(5),
P_COMPONENT VARCHAR2(5)
);
insert into test values ('s2','c2','s2','c1');
insert into test values ('s2','c2','s2','c1');
insert into test values ('s3','c3','s3','c2');
insert into test values ('s3','c3','s3','c2');
commit;
Now i need to update .. can this update be done in a single query
get the mfg_id where p_step, p_component = mfg.step , mfg.component
and set parent_id =mfg_id where test.step , test,component = mfg.test,mfg.component
my final table of mfg will be
scott>select * from mfg;
MFG_I STEP COMPONENT PARENT_ID
----- ----- ---------- ----------
m1 s1 c1 null as no parent
m2 s2 c2 m1
m3 s3 c3 m3
m4 s4 c4 null as no record
August 11, 2003 - 9:59 am UTC
not following you
update query
umesh, August 11, 2003 - 11:53 pm UTC
scott>select * from mfg;
MFG_I STEP COMPONENT PARENT_ID
----- ----- ------------ ----------
m1 s1 c1
m2 s2 c2
m3 s3 c3
m4 s4 c4
scott>select * from test;
STEP COMPONENT P_STEP P_COMPONENT
----- ------------ ---------- --------------------
s2 c2 s1 c1
s2 c2 s1 c1
s3 c3 s2 c2
s3 c3 s2 c2
P_step nad p_compoent in test table hold parent step and parent component for test.step, test.component
in "test" ( consider first row) s2,c2 has pareants in (s1,c1) that means s1,c1 is the parent of s2,c2
in "mfg" s1,c1 = m1 and s2,c2 =m2
so in "mfg" i want parent_ids to be updated
in "mfg" s2,c2 will have parent_id = m1
my question is regarding the updation of the mfg.parent_id
PLease let me know wether this could be done in a single query
Thanks for your patience
August 12, 2003 - 8:36 am UTC
if you look up at your original example -- you'll see the output doesn't make sense there (m3 is the parent of m3? there are no s3/c2 pairs, yet test has them and so on)
Anyway, if we de-dup test, add the requisite primary keys needed to make this update UNAMBIGOUS, then yes you can:
ops$tkyte@ORA920> create table mfg( mfg_i varchar2(2), step varchar2(2), component varchar2(2), parent_id varchar2(2) );
Table created.
ops$tkyte@ORA920> alter table mfg add constraint mfg_pk primary key(step,component);
Table altered.
ops$tkyte@ORA920>
ops$tkyte@ORA920> create table test( step varchar2(2), component varchar2(2), p_step varchar2(2), p_component varchar2(2) );
Table created.
ops$tkyte@ORA920> alter table test add constraint test_pk primary key(step,component);
Table altered.
ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> insert into mfg values ( 'm1', 's1', 'c1', null );
1 row created.
ops$tkyte@ORA920> insert into mfg values ( 'm2', 's2', 'c2', null );
1 row created.
ops$tkyte@ORA920> insert into mfg values ( 'm3', 's3', 'c3', null );
1 row created.
ops$tkyte@ORA920> insert into mfg values ( 'm4', 's4', 'c4', null );
1 row created.
ops$tkyte@ORA920>
ops$tkyte@ORA920> insert into test values ( 's2', 'c2', 's1', 'c1' );
1 row created.
ops$tkyte@ORA920> insert into test values ( 's3', 'c3', 's2', 'c2' );
1 row created.
ops$tkyte@ORA920>
ops$tkyte@ORA920> update (
2 select a.mfg_i, a.step, a.component, a.parent_id, c.mfg_i p_mfg_i
3 from mfg a, test b, mfg c
4 where a.step = b.step
5 and a.component = b.component
6 and b.p_step = c.step
7 and b.p_component = c.component )
8 set parent_id = p_mfg_i;
2 rows updated.
ops$tkyte@ORA920>
ops$tkyte@ORA920> select * from mfg;
MF ST CO PA
-- -- -- --
m1 s1 c1
m2 s2 c2 m1
m3 s3 c3 m2
m4 s4 c4
ops$tkyte@ORA920>
update query
umesh, August 13, 2003 - 11:47 pm UTC
Tom
Can we do this way
update mfg b set parent_id = ( select mfg_id from mfg a
where a.step || a.component = ( select p_step || p_component from
test where step||component = b.step || b.component and rownum = 1) and rownum = 1)
of course each of the concatenated columns will be wrapped in a lpad to the length of columns
August 14, 2003 - 7:51 am UTC
maybe -- if you want to run two slow correlated subqueries for every row in the table -- sure. go for it.
I did not verify this for correctness -- thats your job :)
it'll assign RANDOM values basically if the necessary keys are not in place (eg: running the update twice could result in different ANSWERS). I assume you are using rownum=1 in a misguided attempt to avoid "subquery returns too many rows"
also, you never need to concat like that, just use regular sql operations:
scott@ORA920LAP> select * from emp where (ename,empno) = ( select ename, empno from emp where rownum = 1);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
you can say where (c1,c2,c3) = (select a,b,c from .... )
ORDER BY and ROWNUM in update sub-query.
Adam, August 26, 2003 - 11:23 am UTC
Tom,
In response to the first question on this topic you said
"but you better be sure the sub-query returns 0 or 1 records!".
Can I use ORDER BY and ROWNUM < 2 to get a single row from the
sub-query ?
Here is my example.
create table name
( keyname int,
columnName varchar2(25)
);
create table lookup
( keyname int, -- not a unique key --
value varchar2(25),
orderkey int
);
-- the data in my case is as follows --
insert into name values (1, 'Some Value');
insert into name values (2, null);
insert into name values (3, null);
insert into lookup values (2, 'New Value 1', 1);
insert into lookup values (2, 'New Value 2', 2);
insert into lookup values (2, 'New Value 3', 3);
insert into lookup values (3, 'New Value 1', 1);
insert into lookup values (3, 'New Value 2', 2);
insert into lookup values (3, 'New Value 3', 3);
update name n set columnName =
(select value from
(select value
from lookup l
where l.keyname = n.keyname
order by orderkey)
where rownum < 2)
where columnName is not null;
The above query fails with the following error. The problem
is that "name" is not accessible in the inner sub-query (at
second level).
ERROR at line 5:
ORA-00904: "N"."KEYNAME": invalid identifier
How can I workaround this problem ?
Thanks in advance,
-Adam.
August 26, 2003 - 11:28 am UTC
say orderkey is a number between 1 and 9999999999 (10 digits)
columnName = ( select substr(min( to_char(orderKey,'fm0000000000')||value ),11)
from lookup l
where l.keyname = n.keyname )
encode the orderkey to be "minable" and/or "maxable"
substr out the piece you want. you can get the first/last row that way.
Good one
Vijay Anand, August 27, 2003 - 4:50 am UTC
The solution given by Tom and the way he explains things are outstanding.I always used Exists clause for update before, I'll follow the Join Update from now.
update table
a reader, August 28, 2003 - 7:27 am UTC
hi ...
I am using 8.1.5 on Win NT.
what is the efficient way for updating column B in this scnario ..
I want update column B incrementaly by groupping on columns A,B,C ...etc. [ Ignoring ',' '*' ';' ',' '.' ]
here I m groupping on only One Column..
SQL> select * from test;
A B
-------------------- ----------
First,second,third
First,second,third
First,second,third
First;second;third
First;second;third
First;second;third
First*second*third
First*second*third
First.second.third
First.second.third
Six,eight,nine
Six,eight,nine
Six.eight.nine
Six.eight.nine
Six.eight.nine
Six;eight;nine
Six;eight;nine
Six;eight;nine
Six*eight*nine
Six*eight*nine
Six*eight*nine
--- Example ...
SQL> select * from test;
A B
-------------------- ----------
First,second,third 1
First,second,third 1
First,second,third 1
First;second;third 1
First;second;third 1
First;second;third 1
First*second*third 1
First*second*third 1
First.second.third 1
First.second.third 1
Six,eight,nine 2
Six,eight,nine 2
Six.eight.nine 2
Six.eight.nine 2
Six.eight.nine 2
Six;eight;nine 2
Six;eight;nine 2
Six;eight;nine 2
Six*eight*nine 2
Six*eight*nine 2
Six*eight*nine 2
21 rows selected.
A reader, September 23, 2003 - 4:37 pm UTC
Hi Tom,
I want to update one table using other table.
Example.
T1 T2
-------------- -------------
c1 c1
c2 c2
c3 c3
c4 c4
UPDATE T1
SET T1.c2 = ( SELECT T2.c2 FROM T2 WHERE
T1.c1 = T2.c1 )
WHERE T1.c1 IN (
SELECT T2.c1 FROM T2
WHERE T2.c1 = T1.c1 )
Can you please comment, if this ok.
Thanks.
September 23, 2003 - 6:25 pm UTC
hows about
update ( select t1.c2 t1_c2, t2.c2 t2_c2
from t1, t2
where t1.c1 = t2.c1 )
set t1_c2 = t2_c2;
since t2(c1) MUST BE UNIQUE -- it can have a unique/primary key on it and this works faster
A reader, October 22, 2003 - 1:38 pm UTC
update t
set ( x,y,z)
= ( select x,y,z
from t1
where t.x ==t1.x)
where exists
( select x,y,z
from t1
where t.x ==t1.x)
Why is it necessary to give the whole query, woudnt it suffice to just say
update t
set ( x,y,z)
= ( select x,y,z
from t1
where t.x ==t1.x)
where t.x =: some value we know
October 22, 2003 - 6:17 pm UTC
that doesn't make sense. where did you get "some value you know"
the first query with the where exists -- that will update every row in T with a value in T2 given that a row exists in T2 to update t with
your query will update every row in T where t.x = some_value -- if no values exist in T2, t.x,y,z will be set to NULL
they are totally different!
How about a multi-column key on the lookup table?
Jim Cain, January 10, 2004 - 6:45 pm UTC
I have the following:
CREATE TABLE alpha (
id NUMBER(2) PRIMARY KEY,
NAME VARCHAR2(100) UNIQUE,
status CHAR(1)
);
CREATE TABLE beta (
networkid NUMBER(2),
NAME VARCHAR2(100),
PRIMARY KEY (NAME, networkid)
);
INSERT INTO alpha VALUES (1, 'Jim', 'A');
INSERT INTO alpha VALUES (2, 'Eric', 'A');
INSERT INTO alpha VALUES (3, 'Ryan', 'A');
COMMIT;
INSERT INTO beta VALUES (10, 'Jim');
INSERT INTO beta VALUES (10, 'Eric');
INSERT INTO beta VALUES (20, 'Ryan');
COMMIT;
UPDATE (
SELECT a.status
FROM alpha a, beta b
WHERE a.NAME = b.NAME
AND b.networkid = 10
) SET status = 'X';
Why is this join not key-preserved? I have specified both columns of beta's key.
January 11, 2004 - 6:04 am UTC
Looks like a limitation of the key preserved code -- it wants actual "join columns" -- not a constant value. (you and i know it is the same, but the software doesn't)
In 9i, this works:
ops$tkyte@ORA10G> merge into alpha a
2 using ( select b.*
3 from alpha a, beta b
4 where networkid = 10
5 and a.name = b.name ) b
6 on ( a.name = b.name )
7 when matched then update set status = 'X'
8 when not matched then insert (id) values ( null );
2 rows merged.
merge is neat -- it can get around that key preserved issue nicely. in 10g, this simplifies to:
ops$tkyte@ORA10G> merge into alpha a
2 using ( select b.* from beta b where networkid = 10 ) b
3 on ( a.name = b.name )
4 when matched then update set status = 'X'
5 /
2 rows merged.
since you can leave out either the update or insert clause (and even add a delete if you like)
Using MERGE to simulate an update join...
Gary, January 20, 2004 - 4:57 pm UTC
Tom
Thanks once again for your tireless efforts. Ths site is truly the best resource for us.
When you gave the example of using the following MERGE statement to simulate, as I read the thread, the update join scenario:
merge into alpha a
using ( select b.*
from alpha a, beta b
where networkid = 10
and a.name = b.name ) b
on ( a.name = b.name )
when matched then update set status = 'X'
when not matched then insert (id) values ( null );
I'm not sure I understand how you can insert a row into 'a' using a primary key value of 'null'.
I created a similar test case and got the expected (by me anyway) primary key constraint violation. What did I miss?
Also, the first test case I tried used the format you specified in another thread for the update of a join. In my case, one of my join columns needed an RPAD function applied to it to match a VARCHAR2 column in my table being updated to a CHAR column in the table providing the update values.
I couldn't get the system to recognize I was trying to do an update of a join and I kept getting the infamous 'Can't update a column not supported by a key preserved table'.
In this case I had a primary key defined on the CHAR column of the 2nd table but still got the message. I assumed it was because I was using a function on the other column even though it was not on my key preserved table.
Here is my update statement and table defs:
desc t
Name Null? Type
----------------- -------- ------------
MODEL NOT NULL VARCHAR2(25)
FLAG CHAR(1)
desc u
Name Null? Type
----------------- -------- ------------
MODEL_NBR CHAR(25)
UPD_FLAG CHAR(1)
update
2 (select t.flag flag, u.upd_flag upd_flag
3 from t t, u u
4 where rpad(t.model, 25) = u.model_nbr)
5* set flag = upd_flag
gww@dwp1> /
set flag = upd_flag
*
ERROR at line 5:
ORA-01779: cannot modify a column which maps to a non key-preserved table
Here are my constraints:
CONSTRAINT C TABLE_NAME INDEX_NAME
---------- - ---------- ----------
T_PK P T T_IDX
U_PK P U U_IDX
The primary key constraints are on the MODEL and MODEL_NBR columns respectively.
This update of a join is very powerful and I would like to use it more if I can get by this llittle snag.
Any help is greatly appreciated. BTW. Your books are great!
G/
January 20, 2004 - 10:02 pm UTC
you cannot insert the null rows -- it is just that in 9i you HAVE to have the insert part -- but it'll never be executed in this case! the join in the using takes care of that. in 10g, you can leave the insert part off and simplify the entire thing.
your test case must have caused the insert branch to actually execute. MY example, with the join, ensures only that matches would be processed.
update/join syntax with a function
Gary, January 27, 2004 - 5:04 pm UTC
Tom
Thanks very much for the MERGE info. I got that to work very nicely. Can you comment on the error returned when using the update/join syntax?
Here is my setup:
Table T:
Name Null? Type
----------------- -------- ------------
MODEL NOT NULL VARCHAR2(25)
FLAG CHAR(1)
Table U:
Name Null? Type
----------------- -------- ------------
MODEL_NBR NOT NULL CHAR(25)
UPD_FLAG CHAR(1)
Constraints and indexes showing primary keys in place:
TABLE_NAME C INDEX_NAME
-------------------- - --------------------
T P T_IDX
U P U_IDX
TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_POSITION
---------- ---------- ----------- ---------------
T T_IDX MODEL 1
U U_IDX MODEL_NBR 1
When trying to use the update/join syntax in this case since I have a VARCHAR2 column that needs to join with a CHAR column, I RPAD the column in the table I'm trying to update in order to leave the column name alone in the secondary table so the primary key is used to maintain the key preservation.
Here is my update statement and results:
update
(select t.model model, t.flag flag, u.model_nbr model_nbr, u.upd_flag upd_flag
from t t, u u
where rpad(t.model, 25) = u.model_nbr)
set flag = upd_flag
gww@dwp1> /
set flag = upd_flag
*
ERROR at line 5:
ORA-01779: cannot modify a column which
maps to a non key-preserved table
Is the use of a function on one side or the other or both causing the error?
I also tried with a function based index as the primary key on the secondary table using the TRIM function but to no avail.
Environment: AIX 4.3.3
ORACLE 9.2.0.3
Any suggestions are most welcome. The MERGE syntax is the direction I'm headed but this particular issue has become a crusade.
Great site! Great books! Many thanks from your groupies!
/G
January 28, 2004 - 8:13 am UTC
it is the function. for all the optimizer knows, rpad(t.model,25) makes t.model "not unique" anymore (and it could) hence there is no "primary key"/"unique constraint" in place here.
Null Update?
Rob, February 05, 2004 - 2:54 pm UTC
You show how to insert null, what about a merge that updates nothing (really a insert into where not exists)
February 06, 2004 - 8:24 am UTC
that would be -- well, insert where not exists i suppose?
insert into table_to_merge_into ( .... )
select table_to_merge_with
from table_to_merge_with
where (key_col1, key_col2, .... ) not in ( select key_col1, key_col2, ....
from table_to_merge_into );
using the cbo and assuming key_colN are "NOT NULL", that'll do a nice speedy hash anti-join.
Rob, February 06, 2004 - 9:52 am UTC
Yes, I realized the simplicy after, I went for a
insert into tablea TA (ID)
(select ID from tableb TB left join TA on TB.ID=TA.ID where TA.ID is null)
another key not preserved problem
Ilya, February 06, 2004 - 2:50 pm UTC
Hi Tom,
I'm having a problem with this update:
CREATE TABLE ALPHA_TEMP (
SEQ_NUM INTEGER NOT NULL ,
NUM_KEY INTEGER NULL ,
NUM_ARR_STR VARCHAR2 (420) NULL ,
ENTITY_TTL INTEGER NULL ,
STOP_DSC VARCHAR2 (255) NULL ,
STOP_DSC_SZ INTEGER NULL ,
MANUAL VARCHAR2 (1) NULL ,
COMMENTS VARCHAR2 (150) NULL ,
CR_OPR VARCHAR2 (9) NULL ,
CR_DATE DATE DEFAULT (SYSDATE) NULL ,
CONSTRAINT PK_ALPHA_TEMP PRIMARY KEY
(
SEQ_NUM
)
)
CREATE TABLE BETA_ENT_TEMP (
SEQ_NUM INTEGER NOT NULL ,
ENTITY_TTL INTEGER NULL ,
ENTITY_NUMS VARCHAR2 (420) NULL
)
UPDATE (SELECT a.entity_ttl AS a_entity_ttl,
b.entity_ttl AS b_entity_ttl
FROM alpha_temp a INNER JOIN
(SELECT seq_num, SUM(entity_ttl) AS entity_ttl
FROM beta_ent_temp
GROUP BY seq_num) b ON a.seq_num = b.seq_num)
SET a_entity_ttl = b_entity_ttl;
This gives me ORA-01779 cannot modify column that maps
to a non-key-preserved table
I am really stumped, because I'm updating column from
alpha_temp, which has a primary key on seq_num
and I'm also joining on primary key column with another table, which should guarantee that the resulting joined row is matched to the main table.
As far as data, I tried various things with the same result, but in the simplest case alpha_temp has one row and beta_ent_temp is empty.
If I put a primary key on beta_ent_temp seq_num, it works, but that table is shared with another program and I cannot modify it.
thanks,
ilya
February 07, 2004 - 1:57 pm UTC
the table being JOINED TO (not being updated) is the one that needs the primary key and even though you and I *know* the group by is building an implicit "primary key sort of constraint", it is not being recognized as such.
so.... you can use merge:
ops$tkyte@ORA9IR2> merge into alpha_temp A
2 using ( select seq_num, sum(entity_ttl) entity_ttl
3 from beta_ent_temp
4 group by seq_num ) B
5 on ( a.seq_num = b.seq_num )
6 when matched then update set a.entity_ttl = b.entity_ttl
7 when not matched then insert ( seq_num ) values ( NULL )
8 /
5 rows merged.
<b>Now, if you do that and you get an error because B has seq_nums that are not in A, eg:</b>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into beta_ent_temp
2 select 55, 55, null from dual;
1 row created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> merge into alpha_temp A
2 using ( select seq_num, sum(entity_ttl) entity_ttl
3 from beta_ent_temp
4 group by seq_num ) B
5 on ( a.seq_num = b.seq_num )
6 when matched then update set a.entity_ttl = b.entity_ttl
7 when not matched then insert ( seq_num ) values ( NULL )
8 /
merge into alpha_temp A
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("OPS$TKYTE"."ALPHA_TEMP"."SEQ_NUM")
<b>You would have to (in 9i) filter the data in B:</b>
ops$tkyte@ORA9IR2> merge into alpha_temp A
2 using ( select b.seq_num, sum(b.entity_ttl) entity_ttl
3 from beta_ent_temp b, alpha_temp a
4 where a.seq_num = b.seq_num
5 group by b.seq_num ) B
6 on ( a.seq_num = b.seq_num )
7 when matched then update set a.entity_ttl = b.entity_ttl
8 when not matched then insert ( seq_num ) values ( NULL )
9 /
5 rows merged.
<b>
but in 10g, you would only need to leave out the when not matched</b>
ops$tkyte@ORA10G> merge into alpha_temp A
2 using ( select seq_num, sum(entity_ttl) entity_ttl
3 from beta_ent_temp
4 group by seq_num ) B
5 on ( a.seq_num = b.seq_num )
6 when matched then update set a.entity_ttl = b.entity_ttl
7 /
Update a table from another table
Steve Booth, February 09, 2004 - 8:42 am UTC
Thank You! I spent four hours trying to figure this out before coming here. This isn't an intuititively obvious answer, but I don't think I'll be forgetting it.
JHT, February 23, 2004 - 11:04 am UTC
Tom,
From a previous posting, you recommended...
update test
set pants = (select max(pants)
from test2
where test.ssn = test2.ssn)
where pants > to_date( '01-JAN-2003', 'dd-mon-yyyy' )
and exists (select pants
from test2
where test.ssn = test2.ssn);
I have a similar SQL as this one (except that it's a join of 3 tables instead of just 2 tables). When a "max(pants)" is required, is there a way to change this to updating a join view. I'm using 8i, but also interested in if using 9i will make any difference (e.g. using the merge option). Thanks.
February 23, 2004 - 4:26 pm UTC
using merge, sure.
merge into test
using ( select ..... from ...... ) test2
on (join condition)
test2 can in effect "be anything at all"
Slow
A reader, February 23, 2004 - 11:04 am UTC
Hi,
The merge is good for non-key problem, but it was kind slow when I ran it with only couple of hundreds records for each two table. DB: 9i
thanks
February 23, 2004 - 4:26 pm UTC
that is not a very scientific sort of statement.
It is kind of blindingly fast for me.
Show us the test case and we'll tell you why in your case it was (and would have to be) "kind of slow".
JHT, February 24, 2004 - 8:44 am UTC
Thanks Tom, but what about in 8i ("merge" not available), is there a way to convert this to an update of a join?
update test
set pants = (select max(pants)
from test2
where test.ssn = test2.ssn)
where pants > to_date( '01-JAN-2003', 'dd-mon-yyyy' )
and exists (select pants
from test2
where test.ssn = test2.ssn);
February 24, 2004 - 9:08 am UTC
you'd have to use a gtt - flow the results of
select ssn, max(pants) max_pants
from test2
group by ssn;
into a gtt with a primary key on ssn and then update the join
update (select pants, max_pants
from test, gtt
where test.ssn = gtt.ssn
and test.pants > to_date( .... ) )
set pants = max_pants;
A reader, May 27, 2004 - 7:04 pm UTC
slightly different situation
GMA, August 04, 2004 - 5:07 pm UTC
Hi Tom,
This was a very useful example, BUT
I have a slightly different situation. I shall provide a little prototype:
Table T1 -- 5 columns, with a unique index on C4 and C5
Table T2 -- 3 columns, with C1 as the primary key
I need to update T2 based on values in T1 joined by C1, with a filter on C4 and C5. Therefore, even though the query returns a unique row, I get the "non key-preserved table error", since the joining column does not have a unique index.
Is there a workaround, or am I missing something?
Here are the scripts:
CREATE TABLE T1 (
C1 NUMBER,
C2 NUMBER,
C3 DATE,
C4 NUMBER,
C5 NUMBER);
CREATE UNIQUE INDEX T1_U01 ON
T1(C4, C5);
CREATE TABLE T2 (
C1 NUMBER PRIMARY KEY,
C2 NUMBER,
C3 DATE);
insert into t2 values (1, 123, sysdate);
insert into t2 values (2, 456, NULL);
insert into t2 values (3, 789, NULL);
insert into t1 values (1, 678, sysdate,1,1);
insert into t1 values (1, 908, sysdate,1,2);
insert into t1 values (1, 345, NULL,1,3);
insert into t1 values (2, 678, sysdate,2,1);
insert into t1 values (2, 908, sysdate,2,2);
insert into t1 values (2, 345, NULL,2,3);
This is the update statement
update (
select t1.c2 t1c2, t2.c2 t2c2
from t1, t2
where t2.c1 = t1.c1
and
t1.c4 = 1 AND
t1.c5 = 2
)
set t2c2 = t2c2 + t1c2
Is there a way I can do this without the multiple subqueries?
August 05, 2004 - 10:02 am UTC
ops$tkyte@ORA9IR2> set autotrace on explain
ops$tkyte@ORA9IR2> merge into t2
2 using ( select t2.rowid rid, t1.c2
3 from t1, t2
4 where t1.c1 = t2.c1
5 and t1.c4 = 1
6 and t1.c5 = 2 ) x
7 on ( t2.rowid = x.rid )
8 when matched then update set t2.c2 = t2.c2 + x.c2
9 when not matched then insert ( c1 ) values (NULL);
1 row merged.
Execution Plan
----------------------------------------------------------
0 MERGE STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=61)
1 0 MERGE OF 'T2'
2 1 VIEW
3 2 NESTED LOOPS (OUTER) (Cost=2 Card=1 Bytes=114)
4 3 NESTED LOOPS (Cost=1 Card=1 Bytes=72)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=1 Card=1 Bytes=52)
6 5 INDEX (RANGE SCAN) OF 'T1_U01' (UNIQUE) (Cost=1 Card=1)
7 4 INDEX (UNIQUE SCAN) OF 'SYS_C005924' (UNIQUE)
8 3 TABLE ACCESS (BY USER ROWID) OF 'T2' (Cost=1 Card=1 Bytes=42)
ops$tkyte@ORA9IR2> set autotrace off
the when not matched never happens since we are joining by rowid and the rowids come from t2.
Update from another table
KJ, August 05, 2004 - 9:39 pm UTC
Hi Tom
I have table A that has a millions of rows.
Table A ( C1, C2 ... ,NAME, C10);
TABLE B ( VALUE, NAME)
Table B holds 00, 'namea' ... 99, 'name 99'
I am supposed to replace NAME in table A with Table B name based on the rownumber. Table A row1 NAME should become 'namea' .. TABLE A row99 NAME should become 'name 99'. Table A row 100 should again become 'namea' , TableA row199 should become 'name99' .. and so on.
I tried using UPDATE with rownum and it does not work.
Could you give me a sample code? This happens in a procedure for a # of tables ( Sort of masking data). So,I cannot use any key and the tables may be different structure. I have to use rownumber to update this.
Thanks for your help
August 06, 2004 - 8:02 am UTC
what is "row1" in a relational database? there is no such "concept".
start by defining to me what "row 1" is.
Update followup
KJ, August 06, 2004 - 10:46 am UTC
Hi Tom
I meant the rownum =1 when do you do a select * from table A.
TABLE A: TABLE B
C1 C2 NAME VALUE NAME
12 34 TEST1 00 MNAME0
1 3 TEST3 01 MNAME1
5 89 TEST5 02 MNAME2
.. ..
90 78 TEST9 99 MNAME99
What needs to happen is TEST1 is to be replaced with MNAME0
TEST3 with MNAME1 and so on ... A
After the first 100 rows in Table A are replaced with the 100 NAMES in TABLE B, The next 100 rows from A are to be replaced again with the same MNAME0-MNAME99 from B.
Is this clear? I tried to use something like MOD(rownum, 100) = B.Value. That didnt help.
August 06, 2004 - 11:48 am UTC
are you just trying to "randomly" assign 100 values to your rows (given that there is no such thing as "row 42" in a table.
i would
update t set x = decode( rownum, 1, 'value 1',
2, 'value 2', .... );
take the values in the table and make them be the values in the decode (if you write, write sql to query tableb to create the decode, spool it out and use that)
thank you
GMA, August 10, 2004 - 1:41 pm UTC
I shall keep that in mind -- we are currently still on 8.1.7, but can incorporate the merge when we upgrade
Update date fields between two tables
Dulal, August 15, 2004 - 8:02 am UTC
I am facing problems with updating date fields.
I have Table1 & Table2 tables and i wish to update date fields of Table2 table after saving a record in Table1 from screen by pressing save button with trigger WHEN_BUTTON_ PRESS. Boths fields are date fields.
with in WHEN-BUTTON_PRESS trigger I have written codes as
commit_form;
update Table2 set Table2.join_date=(select Table1.join_date from Table1 where Table1.EMPID=:Block_Employee.EMPID) where Table2.EMPID=:Block_Employee.EMPID;
commit;
its working but the date is changing.
1) if the date in Table1 is to_char('01/01/1972','dd/mm/rrrr') then it updating as to_char('01/01/2072','dd/mm/rrrr') with in Table2.
2) if the date in Table1 is to_char('01/01/3005','dd/mm/rrrr') then it updating as to_char('01/01/2005','dd/mm/rrrr') with in Table2.
When I am trying from SQL Prompt then it working well.
The problems are like as Y2K problems.
Kindly gives me a solution.
I am using Oralce9i(res.2.0), Dev6i, Windows2000 Server,
Thanks in advance.
August 15, 2004 - 9:23 am UTC
Impossible give the information supplied and the assumptions I have to made (that you are using the DATE datatype in the columns.
You need to provide me a complete example, as you have stated it, quite simply "not possible"
ops$tkyte@ORA9IR2> variable empid number
ops$tkyte@ORA9IR2> exec :empid := 1
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table t1 ( join_date date, empid int );
Table created.
ops$tkyte@ORA9IR2> create table t2 ( join_date date, empid int );
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t1 values ( to_date( '01/01/1972', 'dd/mm/rrrr'), 1 );
1 row created.
ops$tkyte@ORA9IR2> insert into t2 values ( null, 1 );
1 row created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> update t2 set join_date = (select join_date from t1 where empid = :empid )
2 where empid = :empid;
1 row updated.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select to_char(join_date,'dd-mon-yyyy') from t1;
TO_CHAR(JOI
-----------
01-jan-1972
ops$tkyte@ORA9IR2> select to_char(join_date,'dd-mon-yyyy') from t2;
TO_CHAR(JOI
-----------
01-jan-1972
You must be doing something *very different* for forms is not any different than sqlplus is as far as "running sql" is concerned.
You are apparently doing an implict "date to string to date" conversion in there somewhere. the date to string is using the default date format (probably rr or yy -- losing the century). the implicate string to date is using the yy format (i'm thinking you have a default date format of yy, not rr).
So, the example is not as you have layed out -- or one of the join dates is really "a string", not a date.
Thanks Tom.
Dulal, August 16, 2004 - 1:21 am UTC
My problems solved.
Excellent information
Rajiv Singh, August 19, 2004 - 3:31 pm UTC
Tom, Thanks for the fantastic site. Could you please see the following update:
update
(select c.cf_ind, f.cust_key, f.fin_ind
from customer cust,
(SELECT DISTINCT cust_key, sum(fin_ind) fin_ind FROM
(
SELECT DISTINCT a.cust_key,DECODE(d.bc,'B',2,'J',1,0) bc
FROM deposit d, account a
WHERE d.acct_key=a.acct_key
GROUP BY a.cust_key, d.bc
UNION all
SELECT DISTINCT a.cust_key,DECODE(c.bc,'B',2,'J',1,0) bc
FROM credit c, account a
WHERE c.acct_key=a.acct_key
GROUP BY a.cust_key, c.bc
UNION all
SELECT DISTINCT a.cust_key,DECODE(m.bc,'B',2,'J',1,0) bc
FROM misc m, account a
WHERE m.acct_key=a.acct_key
GROUP BY a.cust_key, m.bc
) group by cust_key
) fact
where cust.cust_key=fact.cust_key
)
set cf_ind=fin_ind
/
set cf_ind=fin_ind
*
ERROR at line 26:
ORA-01779: cannot modify a column which maps to a non key-preserved table
I read about what you said about the key preserved table. In this case, the second table is an inline view, and I have a group by and a distinct there which make sure that the cust_key is unique in the inline view.
Is it possible to do such updates using in-line views (ie how do we create a pk/unique index on the inline view here?).
In this case, if we have a unique index on cust_key column on the table account, will it work? (It is not possible for us to make a uq idex there, becasue one customer can have multiple accounts). So how can I re-write this query?
Thanks again for the best oracle answers.
regards,
RS
August 19, 2004 - 7:57 pm UTC
you would have to use MERGE for that -- the database needs a primary key, unique key
you and i know the group by "should be good enough" but the parser doesn't.
A reader, August 20, 2004 - 12:18 am UTC
Thanks.. But I am still on 8.1.7, sorry.
I used the hint bypass_ujvc to workaround it. It is working. Any comments about this hint?
Also, I tried to re-write the sql as per your answer to the initial question (the second menthod, using where exists). I could not do that. Just for information purpose, could you demonstrate how to re-write this sql in that form?
Thanks for your help. Appreciate the fast response.
Regards,
RS
August 20, 2004 - 10:43 am UTC
that hint is very dangerous -- search for it in this site.
no, i cannot rewrite:
update
(select c.cf_ind, f.cust_key, f.fin_ind
from customer cust,
(SELECT DISTINCT cust_key, sum(fin_ind) fin_ind FROM
(
SELECT DISTINCT a.cust_key,DECODE(d.bc,'B',2,'J',1,0) bc
FROM deposit d, account a
WHERE d.acct_key=a.acct_key
GROUP BY a.cust_key, d.bc
UNION all
SELECT DISTINCT a.cust_key,DECODE(c.bc,'B',2,'J',1,0) bc
FROM credit c, account a
WHERE c.acct_key=a.acct_key
GROUP BY a.cust_key, c.bc
UNION all
SELECT DISTINCT a.cust_key,DECODE(m.bc,'B',2,'J',1,0) bc
FROM misc m, account a
WHERE m.acct_key=a.acct_key
GROUP BY a.cust_key, m.bc
) group by cust_key
) fact
where cust.cust_key=fact.cust_key
)
set cf_ind=fin_ind
/
because it doesn't make sense to me. where does "fin_ind" come from??
that and I don't have simple create tables -etc etc etc.
A reader, August 20, 2004 - 12:05 pm UTC
Tom,
Sorry, the fin_ind is an alias I used for the bc column:
update
(select c.cf_ind, f.cust_key, f.fin_ind
from customer cust,
(SELECT DISTINCT cust_key, sum(bc) fin_ind FROM
(
SELECT DISTINCT a.cust_key cust_key, DECODE(d.bc,'B',2,'J',1,0) bc
FROM deposit d, account a
WHERE d.acct_key=a.acct_key
GROUP BY a.cust_key, d.bc
UNION all
SELECT DISTINCT a.cust_key cust_key, DECODE(c.bc,'B',2,'J',1,0) bc
FROM credit c, account a
WHERE c.acct_key=a.acct_key
GROUP BY a.cust_key, c.bc
UNION all
SELECT DISTINCT a.cust_key cust_key, DECODE(m.bc,'B',2,'J',1,0) bc
FROM misc m, account a
WHERE m.acct_key=a.acct_key
GROUP BY a.cust_key, m.bc
) group by cust_key
) fact
where cust.cust_key=fact.cust_key
)
set cf_ind=fin_ind
/
My main questions are:
1. When the "lookup" table is an inline view, that is a complex join of many tables, where do we create the pk/unique index? So, is it possible for writing such updates when the lookup table is an inline view?
2. When we can make sure programatically that the lookup table (ie. the inline view) has distinct keys, is there anything wrong in using the bypass_ujvc hint?
3. I will appreciate if you can show me how to rewrite the above sql the other way(using where exists logic). Sorry for posting incorrect sql earlier. If you want I can give you the create table ddls.
thanks again for your time.
regards,
RS
August 21, 2004 - 10:50 am UTC
1) no, you would have to use MERGE
2) yes, it is undocumented, it is unsafe, its behaviour could change in unpredicable ways in the future, it is "magic"
3) it is very straight forward to do this with "where exists", you use a correlated subquery that would run run for each row to get just the sum for that row -- using almost the same query in both places (cannot use the sum() in the where exists since an aggregate without a group by always returns a single row)
update customer
set cf_ind =
(SELECT sum(bc) fin_ind FROM
(
SELECT DISTINCT a.cust_key cust_key, DECODE(d.bc,'B',2,'J',1,0) bc
FROM deposit d, account a
WHERE d.acct_key=a.acct_key
and a.cust_key = customer.cust_key
GROUP BY a.cust_key, d.bc
UNION all
SELECT DISTINCT a.cust_key cust_key, DECODE(c.bc,'B',2,'J',1,0) bc
FROM credit c, account a
WHERE c.acct_key=a.acct_key
and a.cust_key = customer.cust_key
GROUP BY a.cust_key, c.bc
UNION all
SELECT DISTINCT a.cust_key cust_key, DECODE(m.bc,'B',2,'J',1,0) bc
FROM misc m, account a
WHERE m.acct_key=a.acct_key
and a.cust_key = customer.cust_key
GROUP BY a.cust_key, m.bc
)
)
where exists
(SELECT (bc) fin_ind FROM
(
SELECT DISTINCT a.cust_key cust_key, DECODE(d.bc,'B',2,'J',1,0) bc
FROM deposit d, account a
WHERE d.acct_key=a.acct_key
and a.cust_key = customer.cust_key
GROUP BY a.cust_key, d.bc
UNION all
SELECT DISTINCT a.cust_key cust_key, DECODE(c.bc,'B',2,'J',1,0) bc
FROM credit c, account a
WHERE c.acct_key=a.acct_key
and a.cust_key = customer.cust_key
GROUP BY a.cust_key, c.bc
UNION all
SELECT DISTINCT a.cust_key cust_key, DECODE(m.bc,'B',2,'J',1,0) bc
FROM misc m, account a
WHERE m.acct_key=a.acct_key
and a.cust_key = customer.cust_key
GROUP BY a.cust_key, m.bc
)
)
/
Thanks for your time, but....
A reader, August 22, 2004 - 2:23 pm UTC
....but I think the logic is flawed. You are setting the cf_ind to the sum(bc) without any group by, so that will set every column to the same value regardless of the join condition. That is not what is required. I am selecting, cust_key and sum(bc) with a group by on cust_key and updating corresponding values based on the match on cust_key. I am not updating the same value to all the rows as it is being done in this query.
Actually I tried to rewrite the query earlier based on the approach of using the same query twice, once in the update statement and once in the where exists clause. But since, the update has to be based on the join, I could not do it.
Hope you understand what I am saying.. I think it is not possible to re-write this query using where exists properly. We have to use either merge, or the hint in 8.1.7. Any comments?
Regards,
RS
August 22, 2004 - 5:06 pm UTC
logic not flawed.
you grouped by and then joined on cust_key.
I am predicating on it:
update customer
set cf_ind =
(SELECT sum(bc) fin_ind FROM
(
SELECT DISTINCT a.cust_key cust_key, DECODE(d.bc,'B',2,'J',1,0) bc
FROM deposit d, account a
WHERE d.acct_key=a.acct_key
and a.cust_key = customer.cust_key
....
)
)
i get the sum(bc) FOR A GIVEN CUST_KEY
just as you did.
please see this
A reader, August 23, 2004 - 4:33 pm UTC
I created a sample customer table for you:
rsingh@DWTEST>select * from customer;
CUST_KEY CUST_NAME C
---------- ------------------------------ -
652 AIG EUROPE UK LIMITED 1
1573 BERNARD C HARRIS PUBLISHING CO 1
2163 BRODERICK,KEVIN 1
2426 BURTON SUPPLY COMPANY INCORPOR 1
2823 CAVANAUGH,ANDREW J 1
3770 CUGINI,JOSEPH D 1
4127 DELGADILLO,DIANE L 1
7238 HARRIS,ROBERT O 1
8106 JACOBY,ROBERT 1
16174 STEINER,NANCY E. 1
10 rows selected.
Elapsed: 00:00:00.02
rsingh@DWTEST>
rsingh@DWTEST>
rsingh@DWTEST> update /*+bypass_ujvc*/
2 (select c.cf_ind, f.cust_key, f.fin_ind
3 from customer c,
4 (SELECT DISTINCT cust_key, sum(bc) fin_ind FROM
5 (
6 SELECT DISTINCT a.cust_key cust_key, DECODE(d.bc,'B',2,'J',1,0) bc
7 FROM deposit d, account a
8 WHERE d.acct_key=a.acct_key
9 GROUP BY a.cust_key, d.bc
10 UNION all
11 SELECT DISTINCT a.cust_key cust_key, DECODE(c.bank_code,'B',2,'J',1,0) bc
12 FROM credit c, account a
13 WHERE c.acct_key=a.acct_key
14 GROUP BY a.cust_key, c.bank_code
15 UNION all
16 SELECT DISTINCT a.cust_key cust_key, DECODE(m.bank_code,'B',2,'J',1,0) bc
17 FROM misc m, account a
18 WHERE m.acct_key=a.acct_key
19 GROUP BY a.cust_key, m.bank_code
20 ) group by cust_key
21 ) f
22 where c.cust_key=f.cust_key
23 )
24 set cf_ind=fin_ind
25 /
10 rows updated.
Elapsed: 00:00:14.05
rsingh@DWTEST>select * from customer;
CUST_KEY CUST_NAME C
---------- ------------------------------ -
652 AIG EUROPE UK LIMITED 3
1573 BERNARD C HARRIS PUBLISHING CO 3
2163 BRODERICK,KEVIN 3
2426 BURTON SUPPLY COMPANY INCORPOR 3
2823 CAVANAUGH,ANDREW J 3
3770 CUGINI,JOSEPH D 1
4127 DELGADILLO,DIANE L 3
7238 HARRIS,ROBERT O 1
8106 JACOBY,ROBERT 2
16174 STEINER,NANCY E. 3
10 rows selected.
Elapsed: 00:00:00.01
rsingh@DWTEST>
rsingh@DWTEST>rollback;
Rollback complete.
Elapsed: 00:00:00.00
rsingh@DWTEST>update customer
2 set cf_ind =
3 (SELECT sum(bc) fin_ind FROM
4 (
5 SELECT DISTINCT a.cust_key cust_key, DECODE(d.bc,'B',2,'J',1,0) bc
6 FROM deposit d, account a, customer
7 WHERE d.acct_key=a.acct_key
8 and a.cust_key = customer.cust_key
9 GROUP BY a.cust_key, d.bc
10 UNION all
11 SELECT DISTINCT a.cust_key cust_key, DECODE(c.bc,'B',2,'J',1,0) bc
12 FROM credit c, account a, customer
13 WHERE c.acct_key=a.acct_key
14 and a.cust_key = customer.cust_key
15 GROUP BY a.cust_key, c.bc
16 UNION all
17 SELECT DISTINCT a.cust_key cust_key, DECODE(m.bc,'B',2,'J',1,0) bc
18 FROM misc m, account a, customer
19 WHERE m.acct_key=a.acct_key
20 and a.cust_key = customer.cust_key
21 GROUP BY a.cust_key, m.bc
22 )
23 )
24 where exists
25 (SELECT (bc) fin_ind FROM
26 (
27 SELECT DISTINCT a.cust_key cust_key, DECODE(d.bc,'B',2,'J',1,0) bc
28 FROM deposit d, account a, customer
29 WHERE d.acct_key=a.acct_key
30 and a.cust_key = customer.cust_key
31 GROUP BY a.cust_key, d.bc
32 UNION all
33 SELECT DISTINCT a.cust_key cust_key, DECODE(c.bc,'B',2,'J',1,0) bc
34 FROM credit c, account a, customer
35 WHERE c.acct_key=a.acct_key
36 and a.cust_key = customer.cust_key
37 GROUP BY a.cust_key, c.bc
38 UNION all
39 SELECT DISTINCT a.cust_key cust_key, DECODE(m.bc,'B',2,'J',1,0) bc
40 FROM misc m, account a, customer
41 WHERE m.acct_key=a.acct_key
42 and a.cust_key = customer.cust_key
43 GROUP BY a.cust_key, m.bc
44 )
45 )
46 /
(SELECT sum(bc) fin_ind FROM
*
ERROR at line 3:
ORA-01401: inserted value too large for column
Elapsed: 00:00:35.08
rsingh@DWTEST>select * from customer;
CUST_KEY CUST_NAME C
---------- ------------------------------ -
652 AIG EUROPE UK LIMITED 1
1573 BERNARD C HARRIS PUBLISHING CO 1
2163 BRODERICK,KEVIN 1
2426 BURTON SUPPLY COMPANY INCORPOR 1
2823 CAVANAUGH,ANDREW J 1
3770 CUGINI,JOSEPH D 1
4127 DELGADILLO,DIANE L 1
7238 HARRIS,ROBERT O 1
8106 JACOBY,ROBERT 1
16174 STEINER,NANCY E. 1
10 rows selected.
Elapsed: 00:00:00.01
rsingh@DWTEST>
August 23, 2004 - 4:44 pm UTC
and why are you joining to customer in there?!?
I did not.
A reader, August 24, 2004 - 9:53 am UTC
how else will the join in the subquery work? You have a join condition on customer.cust_key, so don't you need customer in the from?
I thought you missed it, so I just added it.
odsowner@ODS3>ed
Wrote file afiedt.buf
1 update customer
2 set cf_ind =
3 (SELECT sum(bc) fin_ind FROM
4 (
5 SELECT DISTINCT a.cust_key cust_key, DECODE(d.bc,'B',2,'J',1,0) bc
6 FROM deposit d, account a
7 WHERE d.acct_key=a.acct_key
8 and a.cust_key = customer.cust_key
9 GROUP BY a.cust_key, d.bc
10 UNION all
11 SELECT DISTINCT a.cust_key cust_key, DECODE(c.bc,'B',2,'J',1,0) bc
12 FROM credit c, account a
13 WHERE c.acct_key=a.acct_key
14 and a.cust_key = customer.cust_key
15 GROUP BY a.cust_key, c.bc
16 UNION all
17 SELECT DISTINCT a.cust_key cust_key, DECODE(m.bc,'B',2,'J',1,0) bc
18 FROM misc m, account a
19 WHERE m.acct_key=a.acct_key
20 and a.cust_key = customer.cust_key
21 GROUP BY a.cust_key, m.bc
22 )
23 )
24 where exists
25 (SELECT (bc) fin_ind FROM
26 (
27 SELECT DISTINCT a.cust_key cust_key, DECODE(d.bc,'B',2,'J',1,0) bc
28 FROM deposit d, account a
29 WHERE d.acct_key=a.acct_key
30 and a.cust_key = customer.cust_key
31 GROUP BY a.cust_key, d.bc
32 UNION all
33 SELECT DISTINCT a.cust_key cust_key, DECODE(c.bc,'B',2,'J',1,0) bc
34 FROM credit c, account a
35 WHERE c.acct_key=a.acct_key
36 and a.cust_key = customer.cust_key
37 GROUP BY a.cust_key, c.bc
38 UNION all
39 SELECT DISTINCT a.cust_key cust_key, DECODE(m.bc,'B',2,'J',1,0) bc
40 FROM misc m, account a
41 WHERE m.acct_key=a.acct_key
42 and a.cust_key = customer.cust_key
43 GROUP BY a.cust_key, m.bc
44 )
45* )
odsowner@ODS3>/
and a.cust_key = customer.cust_key
*
ERROR at line 8:
ORA-00904: "CUSTOMER"."CUST_KEY": invalid identifier
August 24, 2004 - 10:36 am UTC
it is the inline view of the inline view.
No, you would NOT join to customer, you are trying to send CUST_KEY down from the OUTER MOST query - as a correlation variable.
so, we need to move the correlation out one level, consider:
ops$tkyte@ORA9IR2> create table t ( cust_key number, cf_ind number );
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table t1 ( cust_key number, bc int );
Table created.
ops$tkyte@ORA9IR2> create table t2 ( cust_key number, bc int );
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( 1, null );
1 row created.
ops$tkyte@ORA9IR2> insert into t values ( 2, null );
1 row created.
ops$tkyte@ORA9IR2> insert into t values ( 3, 0 );
1 row created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t1 values ( 1, 1 );
1 row created.
ops$tkyte@ORA9IR2> insert into t1 values ( 1, 1 );
1 row created.
ops$tkyte@ORA9IR2> insert into t1 values ( 2, 1 );
1 row created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t2 values ( 2, 1 );
1 row created.
ops$tkyte@ORA9IR2> insert into t2 values ( 2, 1 );
1 row created.
ops$tkyte@ORA9IR2> insert into t2 values ( 1, 1 );
1 row created.
ops$tkyte@ORA9IR2> commit;
Commit complete.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from t;
CUST_KEY CF_IND
---------- ----------
1
2
3 0
ops$tkyte@ORA9IR2> update t
2 set cf_ind = ( select sum(bc) from t1 where t1.cust_key = t.cust_key )
3 where exists
4 ( select (bc) from t1 where t1.cust_key = t.cust_key )
5 /
2 rows updated.
<b>see, that works fine -- you pass the correlation variable DOWN into the subquery, you don't join
but...</b>
ops$tkyte@ORA9IR2> select * from t;
CUST_KEY CF_IND
---------- ----------
1 2
2 1
3 0
ops$tkyte@ORA9IR2> rollback;
Rollback complete.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> update t
2 set cf_ind = ( select sum(bc)
3 from (select bc
4 from t1
5 where t1.cust_key = t.cust_key
6 UNION ALL
7 select bc
8 from t2
9 where t2.cust_key = t.cust_key )
10 )
11 where exists ( select (bc)
12 from (select bc
13 from t1
14 where t1.cust_key = t.cust_key
15 UNION ALL
16 select bc
17 from t2
18 where t2.cust_key = t.cust_key )
19 )
20 /
where t1.cust_key = t.cust_key
*
ERROR at line 5:
ORA-00904: "T"."CUST_KEY": invalid identifier
<b>fails because a correlation variable only goes one level -- t is not known inside of the other one. OK, we do this:</b>
ops$tkyte@ORA9IR2> select * from t;
CUST_KEY CF_IND
---------- ----------
1
2
3 0
ops$tkyte@ORA9IR2> update t
2 set cf_ind = ( select sum(bc)
3 from (select bc , cust_key
4 from t1
5 UNION ALL
6 select bc , cust_key
7 from t2 )
<b> 8 where cust_key = t.cust_key</b>
9 )
10 where exists ( select (bc)
11 from (select bc , cust_key
12 from t1
13 UNION ALL
14 select bc , cust_key
15 from t2 )
<b> 16 where cust_key = t.cust_key</b>
17 )
18 /
2 rows updated.
ops$tkyte@ORA9IR2> select * from t;
CUST_KEY CF_IND
---------- ----------
1 3
2 3
3 0
<b>and there you go
this is why I have a hard time with examples that have NO create tables, i cannot really test anything </b>
Thanks a lot
A reader, August 24, 2004 - 2:06 pm UTC
Thanks Tom, I got it now... Sorry for "doubting" your solution... And sorry for not providing you enought info, my create tables are very big (some have hundreds of fields).
One question though:
update customer set cf_ind=.......
where cust_key=customer.cust_key
isn't this a self join here? And we are doing it twice, once in the update clause and once in the where exists. Will therebe multible FTS on customer due to this? I looked at the plan and found only one FTS on my small test table, I will let you know how it goes in the real tables.
Thanks again for your patience and perseverance.
August 24, 2004 - 3:38 pm UTC
no, it is called a correlated subquery -- it is as if the subquery was run once per row in the outer query.
for each row in customer
loop
run subquery to get sum()
update that row
end loop
fantastic... thanks
A reader, August 24, 2004 - 4:53 pm UTC
Invalid Column Name
VKOUL, August 24, 2004 - 9:08 pm UTC
Could you please explain the following ?
CREATE TABLE SDFOR
(
SEEKER_ID NUMBER(38),
SSN VARCHAR2(11),
ZIPCODE CHAR(5),
MAINT_DATE DATE
);
CREATE TABLE SERVICES
(
SEEKER_ID NUMBER(38),
SSN VARCHAR2(11),
ZIPCODE CHAR(5),
SERVICEDATE DATE
);
SQL> l
1 update services s
2 set (ssn, zipcode) = (SELECT ssn, zipcode
3 FROM (SELECT ssn, zipcode,
4 ROW_NUMBER()
5 OVER (PARTITION BY seeker_id ORDER BY maint_date DESC) rownumber
6 FROM sdfor
7 WHERE sdfor.seeker_id = s.seeker_id
8 AND sdfor.maint_date <= s.servicedate
9 )
10 WHERE rownumber = 1
11* )
SQL>
SQL> /
AND sdfor.maint_date <= s.servicedate
*
ERROR at line 8:
ORA-00904: invalid column name
SQL>
SQL>
SQL> disc
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.3.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.3.0 - 64bit Production
SQL>
Can it be converted to following construct ?
UPDATE (SELECT ...
)
SET ...
Thanks
August 25, 2004 - 7:21 am UTC
correlation names are pushed down ONE level and one level only.
seems like a strange sort of update.
Not that I could actually *test it* as there was no sample create tables and inserts..... but this is the gist:
merge into services s
using ( select *
from ( select s.rowid rid, s2.ssn, s2.zipcode,
row_number() over
(partition by s2.seeker_id
order by s2.maint_date desc) rn
from services s, sdfor s2
where s2.seeker_id = s.seeker_id
and s2.maint_date <= s.servicedate )
where rn = 1 ) x
on ( s.rowid = x.rid )
when matched then update set ssn = x.ssn, zipcode = x.zipcode
when not matched /* never happens! */ then insert (ssn) values ( null );
A reader, September 19, 2004 - 9:13 pm UTC
Tom,
There is a table with 8964385 rows. 59 columns. Primay key on 1 column.
695637 rows having any of three columns (A,B,C) with value 0. These 0s were replaced to nulls.
The # of indexes on the table is 8. All of them except pk (unique) are nonunique. Columns A, B also include. Not C
Estimated time for the operation with an unknown sql is 90 minutes. It however took around 3 hours.
Could you shed some light? Indexes were not disabled before the operation. Only update triggers were disabled. I donot think any kind of tracking is used.
September 19, 2004 - 9:56 pm UTC
sorry -- don't know how you estimated 90 minutes, what "an unknown sql" is or what "tracking" might be.
A reader, September 19, 2004 - 10:10 pm UTC
Tom,
Unknown is the sql that somebody used. It is probably an update statement. That person estimated the operation would take 90 minutes. But it took a lot longer. It took about 3 hours. Tracking is something like tkprof (if i am not wrong).
My questions:
1. Could a plain update statement be issued here (which changes 0s to nulls. If not what should be used?
2. What are the factors that need to be considered before doing an update like that?
3. How long should the update (from the info that you have) might take? I want to know if it is something that could be done in 10 mins, 20 mins ...
September 20, 2004 - 7:49 am UTC
I thought it would take an hour to get somewhere.
I took an unknown route.
It took me lots longer than I thought.....
I wonder...
o did I use a bad route? (inefficient sql)
o did I hit lots of traffic? (contention)
o perhaps there was accident? (hit lots of locks)
o did I take a wrong turn?
I wonder.......
1) there are only "plain updates", there are no fancy or blue ones. the update would have been something like:
update t
set a = decode(a,0,null,a), b = decode(b,0,null,b), c = decode(c,0,null,c)
where a = 0 or b = 0 or c = 0;
2) are you going to contend with lots of other people? are you going to hit lots of row locks because of them? should a massive update to a live table be scheduled during off peak? is my redo sized appropriately for this operation or will I hit lots of checkpoint not complete/archival required? Is my system 100% utilized already and this will just put it over the top?
the normal stuff we think about.
3) It would be a function of the number of indexes on these columns (a,b,c) -- each on that needs be maintained will take more time. It'll be a function of how long it takes to full scan table T. It'll be a function of the load on your system. It'll be a function of the degree of contention.
"Not Long" would be my guess -- unless they tried to outguess the database and did it slow by slow (row by row) with lots of "commits to save time" and stuff (said with sarcasm - committing frequently kills runtime performance, only commit when your transaction is complete).
Consider:
ops$tkyte@ORA9IR2> drop table t;
Table dropped.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table t
2 as
3 select mod(rownum,2) a, mod(rownum,3) b, 0 c, 42 d
4 from big_table.big_table
5 where rownum <= 695637
6 /
Table created.
ops$tkyte@ORA9IR2> create index t_idx1 on t(a,b);
Index created.
ops$tkyte@ORA9IR2> create index t_idx2 on t(b);
Index created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set timing on
ops$tkyte@ORA9IR2> update t
2 set a = decode(a,0,null,a),
3 b = decode(b,0,null,b),
4 c = decode(c,0,null,c)
5 where a = 0 or b = 0 or c = 0
6 /
695637 rows updated.
Elapsed: 00:01:14.48
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select count(case when a is null then 1 end),
2 count( case when b is null then 1 end), count(case when c is null then 1 end )
3 from t;
COUNT(CASEWHENAISNULLTHEN1END) COUNT(CASEWHENBISNULLTHEN1END)
------------------------------ ------------------------------
COUNT(CASEWHENCISNULLTHEN1END)
------------------------------
347818 231879
695637
so, we changed lots of A's, pretty many B's and all of the C's. A little over a minute.
ORA-00904: invalid identifier
PM, November 09, 2004 - 8:25 pm UTC
I tried so many things with this query bt cud'nt succed
declare
counter number := 0;
CURSOR CUR_SICS_ALL IS
SELECT pra.cl_country_loc gusstat FROM cl_location_lst@link_pgus_query pra ;
aSICS CUR_SICS_ALL%ROWTYPE;
BEGIN
for asics in CUR_SICS_ALL loop
IF (aSICS.GUSSTAT IN (9,10) ) THEN
counter := counter + 1;
END IF;
end loop;
dbms_output.put_line('counter'||counter);
END;
Above query gives error.
PL/SQL: ORA-00904: "PRA"."CL_COUNTRY_LOC": invalid identifier
ORA-06550: line 4, column 4:
PL/SQL: SQL Statement ignored
ORA-06550: line 3, column 8:
PLS-00341: declaration of cursor 'CUR_SICS_ALL' is incomplete or malformed
if i am removing CL_COUNTRY_LOC then it works.
SELECT count(1) gusstat FROM cl_location_lst@link_pgus_query pra ;
L/SQL procedure successfully completed
This is the prod prob. Pls help me to resolve this prob.
Thanks a lot !!
November 09, 2004 - 8:56 pm UTC
well, can we at least see a describe of the table?
ORA-00904: invalid identifier
PM, November 10, 2004 - 12:52 am UTC
Thanks for your quick response.
Actually above object is a public synonym cl_location_lst is in other database and in link_pra_query schema.
and nt able to describe the object bt select this object.
Thanks a lot !!
November 10, 2004 - 7:06 am UTC
create view temp as select * from <that object>
desc temp
The LOOKUP is a VIEW
robert, December 03, 2004 - 11:44 am UTC
8.1.7.4
>>
scott@ORA734.WORLD> update
2 ( select columnName, value
3 from name, lookup
4 where name.keyname = lookup.keyname
5 and lookup.otherColumn = :other_value )
6 set columnName = value
<<
Tom, I want to use your preferred way of correlated update above.
my "lookup" is a VIEW like this:
CREATE OR REPLACE VIEW
v_tranx_data
AS
SELECT ma1.tranx_id,
...
...
...
FROM ma1_transactions ma1,
(SELECT tranx_id... FROM ...) ma5,
lkp_one,
lkp_two,
WHERE ma1.c1 = lkp_one.c1 (+)
AND ma1.c2 = lkp_two.c2 (+)
AND ma1.tranx_id = ma5.tranx_id (+)
** There is a PK on MA1_TRANSACTIONS **
alter table MA1_TRANSACTIONS
add primary key (TRANX_ID) using index....
SQL8.1.7.4>
update (select mygtt.*, tranx_lookup.*
from mygtt,
v_tranx_data tranx_lookup
WHERE mygtt.c1 = tranx_lookup.tranx_id)
SET c2 = tranx_yyyy
But got error:
ORA-01779: cannot modify a column which maps to a non key-preserved table
NO PK on the table I want to update (mygtt)
December 03, 2004 - 1:24 pm UTC
you won't be able to do that -- in later releases MERGE will permit you to do that but there are no safe ways in 8i.
if you read this page closely you'll see a very unsafe way to do it. caveat emptor.
How about this SQL
Robert, December 03, 2004 - 5:23 pm UTC
Thanks for the ealier answer.
UPDATE mygtt gtt
SET (ca1, ca2) =
(SELECT SUM(nvl(cb1,0)), SUM(nvl(cb2,0))
FROM ma10_upstream
WHERE tranx_id = gtt.tranx_id)
WHERE c_misc1 = 'U'
AND
EXISTS
(SELECT 'x' FROM ma10_upstream WHERE tranx_id = gtt.tranx_id) ;
the last line of SQL
(SELECT 'x' FROM ma10_upstream WHERE tranx_id = tt.tranx_id)
may return > 1 row.
Am I going to have a problem ?
thanks
December 04, 2004 - 10:45 am UTC
thats what exists is for, just looks for existence and stops as soon as it confirmed it. yes, you can have many rows.
Update Statement
Zubair, December 07, 2004 - 1:42 am UTC
ORACLE 9.2
I have three table A & B & C now i want to update some column of table a
using some cloulmns value from table b
the query will be something like below
update a set a.ins_cost=0.3*b.length where a.zx=b.zx and a.yy=c.yy;
Please help how do i acheive this using single command.
length might have NULL values
Thank you
Zubair
December 07, 2004 - 10:15 am UTC
did you see the original answer? update a join.
Update Statement
Zubair, December 07, 2004 - 11:39 pm UTC
Yes,
But since i had third table also so just wanted to make things myself clear.
from your answer i have derived following statement.
pls let me know whether it is correct ?
update a set ins_cost=0.3*(select b.length from B,C where a.zx=b.zx and a.yy=c.yy )
where exists (select b.length from B,C where a.zx=b.zx and a.yy=c.yy)
Thanks
Zubair
December 08, 2004 - 10:20 am UTC
does it work and give you the right answer? then it is correct.
you can update a join as well.
update and null values
PinguSAN, March 07, 2005 - 4:59 am UTC
Hi
I have this simple update
update dept
set dname = (select dname from dept_hist where deptno = 99)
where deptno = 99
if this updates 0 rows dname in dept becomes NULL however I dont want to have NULLs in this column, instead I need some default value such as
NVL(dname, 'NO_HIST') from dept_hist
is this possible?
Cheers
March 07, 2005 - 8:19 am UTC
set dname = nvl( (select ...), 'NO_HIST' )
thank you
pingusan, March 08, 2005 - 10:41 am UTC
Hi
I was stupid,
thank you
moving correlation o upper level
mile, April 13, 2005 - 11:33 am UTC
Hi Tom,
thaks for your valuable help on this web site. Hope you can help me with this update.
You said (in the above example):
"it is the inline view of the inline view.
No, you would NOT join to customer, you are trying to send CUST_KEY down from
the OUTER MOST query - as a correlation variable.
so, we need to move the correlation out one level"
How could I apply that in the following example in which inner most inline view has statement START WITH ...)? It seems it cannot "go up"!?:
create table T1
(
RECPT NUMBER(11),
FKEY NUMBER(11),
);
create table T2
(
PKEY NUMBER(11),
RECPT NUMBER(11),
SOME_ATTR CHAR(1),
PKEY_171 NUMBER(11),
primary key( pkey )
);
create table T3
(
RECPT NUMBER(11),
FKEY NUMBER(11),
);
Table T2 contains pkey-171 which is linked list of events, child event points to its parent.
This is the problem update statement:
update T1
set fkey = ( select T2.pkey
from T2 t2, T3
where t2.recpt = T1.recpt
and t2.recpt = T3.recpt
and t2.pkey = T3.fkey
and exists (-- check sequence of chained events if there is one with attribute some_attr = '6'
select 'x'
from T2 t22,
(
select substr( history, 2, instr(history,'/',1,2)-2 ) head_event,
substr( history, 2, instr(history,'/',-1,1)+1 ) chained_event
from (
select level depth,
SYS_CONNECT_BY_PATH( pkey,'/' ) history
from T2
start with pkey = t2.pkey -- start with outer most (parent)
connect by prior pkey = pkey_171
)
where depth > 1
) s0
where to_number( s0.chained_event ) = t22.pkey
and to_number( s0.head_event ) = t2.pkey
and t22.some_attr = '6'
)
)
where exists (
select T2.pkey
from T2 t2, T3
where t2.recpt = T1.recpt
and t2.recpt = T3.recpt
and t2.pkey = T3.fkey
and exists (-- check sequence of chained events if there is one with attribute some_attr = '6'
select 'x'
from T2 t22,
(
select substr( history, 2, instr(history,'/',1,2)-2 ) head_event,
substr( history, 2, instr(history,'/',-1,1)+1 ) chained_event
from (
select level depth,
SYS_CONNECT_BY_PATH( pkey,'/' ) history
from T2
start with pkey = t2.pkey -- start with outer most (parent)
connect by prior pkey = pkey_171
)
where depth > 1
) s0
where to_number( s0.chained_event ) = t22.pkey
and to_number( s0.head_event ) = t2.pkey
and t22.some_attr = '6'
)
)
/
I get "invalid identifier" error in "start with pkey = t2.pkey". How can I move that correlation to upper level? (Oracle 9.2)
Many thanks,
April 13, 2005 - 11:48 am UTC
please try to keep followups small and directly relating to original question, this is "big"
privilege
Mile, April 15, 2005 - 3:06 pm UTC
Hi Tom,
I have privilege to execute dbms_sql, but I get this error?!
Which privilege am I missing?
Are there two different dbms_sql packages?
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SYS_SQL", line 826
ORA-06512: at "SYS.DBMS_SQL", line 39
April 15, 2005 - 3:15 pm UTC
you executed dbms_sql, that isn't the problem, you have all privs you need to execute dbms_sql.
what you are missing is the privilege to run the statement you are trying to have dbms_sql run for you.
eg: you are trying to dynamically select from a table you don't have the ability to select from for example
</code>
http://asktom.oracle.com/Misc/RolesAndProcedures.html <code>
might apply as well.
koms
koms, April 15, 2005 - 8:56 pm UTC
Hi Tom,
I am new to Oracle and I have a very strange and non standard requirement.
I have 2 totaly unrelated tables with columns say
T1 ( C1, C2, C3 ) all varchar2 columns
T2 ( C10, C11 ) all varchar2 columns
T1 has 50000 records with data in C1 and C2.
T2 has 20000 records with data in C10 and C11.
I want to copy data from C11 of Table T2, into C3 of table T1 ( both have same type/size ). The copy should be such that the 20000 values in C11 should be approx ( or randomly ) into C3, basically just filling in values from C11 into C3 randomly since row counts do not match.
The output should be 50000 records in C3 with values randomly distributed from column C11 and not just one value into all records.
I already spent 7 hours on this and no output.
April 15, 2005 - 9:12 pm UTC
ops$tkyte@ORA9IR2> create table t1
2 as
3 select object_name c1, object_name c2, object_name c3
4 from big_table.big_table
5 where rownum <= 50000;
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table t2
2 as
3 select object_name c10, rownum c11
4 from big_table.big_table
5 where rownum <= 20000;
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set timing on
ops$tkyte@ORA9IR2> merge into t1
2 using (select rid, c3, c11
3 from (select rownum r, c11
4 from t2) t2,
5 (select rowid rid, mod(rownum,20000)+1 r, c3
6 from t1) t1
7 where t1.r = t2.r ) t2
8 on ( t1.rowid = t2.rid )
9 when matched then update set c3 = c11
10 when not matched then insert (c1) values(null);
50000 rows merged.
Elapsed: 00:00:02.10
ops$tkyte@ORA9IR2> set timing off
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select count(*) from t1 where to_number(c3) > 0;
COUNT(*)
----------
50000
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select avg(count(*)) cnt from t1 group by c3;
CNT
----------
2.5
shows all 50,000 records became numbers (so all were updated) and each record was moved from t2 to t1 2.5 times on average.....
Tricky insert
Bakunian, September 15, 2005 - 5:21 pm UTC
Tom,
I am trying to fix bug and have test case perhaps you can help me. I have complete brain fart.
We use Oracle 9.2 on Solaris
Real table has about 12 million rows. I need insert a new row with activity_key = 114 every time product key changes within particular ID group. I was thinking something like dense_rank or row_number but don't know how to massage it in.
2. Unrelated to above, is there a way to select distinct values without use of DISTINCT/UNIQUE?
create table t (id varchar(20) not null,
date_key number not null,
activity_key number not null,
product_key number not null );
insert into t values ('100', 111, 105, 101);
insert into t values ('100', 111, 105, 102);
insert into t values ('100', 112, 105, 101);
insert into t values ('100', 112, 105, 102);
insert into t values ('100', 113, 105, 102);
insert into t values ('100', 115, 105, 101);
insert into t values ('200', 112, 105, 101);
insert into t values ('200', 113, 105, 102);
commit;
select * from t;
ID DATE_KEY ACTIVITY_KEY PRODUCT_KEY
-------------------- ---------- ------------ -----------
100 111 105 101
100 111 105 102
100 112 105 101
100 112 105 102
100 113 105 102
100 115 105 101
200 112 105 101
200 113 105 102
8 rows selected.
So I am stuck at
insert into t (select id, date_key, 114, product_key
from t t_2
where t_2.id = t.id
and ?????
September 15, 2005 - 5:38 pm UTC
... Real table has about 12 million rows. I need insert a new row with activity_key
= 114 every time product key changes within particular ID group....
need more info - not sure what you mean - take your data and say "after applying this logic <logic goes here> these rows would be inserted.
Also, explain how to "sort" this data.
Tricky insert
Bakunian, September 15, 2005 - 8:19 pm UTC
Table T has id, activity_key and product_key columns.
I am trying to re-insert into T new row of data from itself with value for
activity_key = 114 when value of product_key changes for particular ID.
So I am having trouble coding to identify the change.
See below second row product_key value changed from 101 to 102 this is qualifier for new row to be inserted with ativity_key being set to 114.
ID DATE_KEY ACTIVITY_KEY PRODUCT_KEY
-------------------- ---------- ------------ -----------
100 111 105 101
100 111 105 102
100 112 114 102 <- New re-Inserted row
September 15, 2005 - 9:05 pm UTC
but I see no way to SORT this data to detect a change, how did you sort.
Detecting a change
Bakunian, September 16, 2005 - 1:41 pm UTC
Detecting a change is the fix that I supposed to provide. There is no sorting right now but any kind will do, it is a downstream system being loaded in a batch sort of a data-garret (I don't dare calling it data-warehouse.)
How about #2, is there a way to select distinct values without the use of a DISTINCT operator.
Thank you for looking into this.
September 16, 2005 - 2:09 pm UTC
how can you detect a change in unordered data??!?!? at least in a deterministic fashion?
what if the data goes from a to b to a to b to a to b.
but I retrieve a, a, a, b, b, b
one state change, you retrieve a, a, b, a, b, b
they retreive a, b, a, b, a, b
and so on.....
you could use group by - but.... so what? you want distinct right? nothing says distinct better than that
Very useful, but there seems to be a problem updating self-joined tables
Harry van Thor, December 13, 2005 - 9:45 am UTC
This explains well the sort of update statement I would like to use, but...
I am wondering what the problem is with an update on a self-joined inline view for the test tables in this topic.
See below.
Thanks,
Harry
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> update
2 (
3 select n0.columnname n_0, n1.columnname n_1 from
4 name n0, name n1
5 where n0.keyname=100
6 and n1.keyname=200
7 )
8 set n_0 = n_1 ;
set n_0 = n_1
*
ERROR at line 8:
ORA-01779: cannot modify a column which maps to a non key-preserved table
December 13, 2005 - 12:50 pm UTC
doesn't matter if they are self joined or joined to something else.
The thing you JOIN TO must have a primary key/constraint - you are not joining, you are cartesian "producting" - it is not making the leap to "this'll return exactly one row from n0 and n1 and result in a single row".
There is no join, you can update a join based on a primary key/unique constraint.
You'll use a correlated subquery for this (or merge in 10g with just the update part)
Update a view with Union clause
Thiru, December 21, 2005 - 2:47 pm UTC
Hi Tom,
Though the thread I understand, somehow I am unable to get things going in the following scenario.
CREATE TABLE SM ( ID NUMBER, NUM NUMBER);
CREATE TABLE SL ( ID NUMBER, NUM NUMBER);
CREATE TABLE CK ( ID NUMBER, NUM NUMBER);
ALTER TABLE SM ADD CONSTRAINT SM_PK PRIMARY KEY(ID);
ALTER TABLE SL ADD CONSTRAINT SL_PK PRIMARY KEY(ID);
CREATE INDEX SM_NUM_IDX ON SM(NUM);
CREATE INDEX SL_NUM_IDX ON SL(NUM);
CREATE INDEX CK_IDX ON CK(ID);
CREATE INDEX CK_IDX_NUM ON CK(NUM);
INSERT INTO SM VALUES(1,100);
INSERT INTO SM VALUES(2,200);
INSERT INTO SM VALUES(3,300);
INSERT INTO SL VALUES(1,100);
INSERT INTO SL VALUES(2,200);
INSERT INTO SL VALUES(4,400);
INSERT INTO CK VALUES(1,1);
INSERT INTO CK VALUES(2,2);
INSERT INTO CK VALUES(4,4);
SELECT * FROM SM;
ID NUM
---------- ----------
1 100
2 200
3 300
suh@MSRB> SELECT * FROM SL;
ID NUM
---------- ----------
1 100
2 200
4 400
suh@MSRB> SELECT * FROM CK;
ID NUM
---------- ----------
1 1
2 2
4 4
I would like to update CK and set NUM = (the value of NUM from the UNION of SM and SL)
This is what I did:
UPDATE (
SELECT A.NUM A_NUM,CK.NUM CK_NUM FROM
(SELECT ID,NUM FROM SM
UNION
SELECT ID,NUM FROM SL) A,CK
WHERE A.ID=CK.ID)
SET CK_NUM=A_NUM
ORA-01779: cannot modify a column which maps to a non key-preserved table
Where I am going wrong? This is what the scenario is in the actual tables that the update is required. The primary key is only on SM and SL tables.
December 21, 2005 - 7:59 pm UTC
a union involves a DISTINCT.
but in short, what if SM has:
ID NUM
----- -----
1 55
and SL has:
ID NUM
---- ----
1 42
Now, after the update, what should CK_NUM have for CK.ID = 1???
and why???
that is the reason, it is not deterministic -- it could be 55, it could be 42. depends...
Distinct clause
A reader, December 22, 2005 - 11:44 am UTC
In the case I am presenting and the actual fact for this is that SM and SL table would have the same NUM value for the same ID value.
So if SM give
1 and 55
SL also will give the same.
Is there a way to go about?
December 22, 2005 - 12:20 pm UTC
but, umm, we don't know that - that is the cause of the "not key preserved"
(why two tables with the SAME information - supposedly the same, nothing prevents it from being different)....
either
a) 2 updates, without the union - one after the other
b) correlated subquery update
c) 10g use merge with just the "when matched" part (ignore the when not matched component)
Alternate way
Thiru, December 22, 2005 - 11:48 am UTC
Actually I was looking at doing this update with one single query instead of writing a block of code that I reproduce below. I would like you to comment on a single query if that is possible vs the code below:
declare
l_array_size number default 10000;
type smRec is record(
id dbms_sql.Number_Table,
num dbms_sql.Number_Table
);
cursor C1 is
select id,num from sm
union
select id,num from sl;
l_smrec smRec;
l_done boolean;
begin
open c1;
loop
fetch c1 bulk collect
into l_smrec.id, l_smrec.num LIMIT l_array_size;
l_done := c1%notfound;
forall i in 1 .. l_smrec.id.count
update ck
set ck.num = l_smrec.num(i)
where ck.id = l_smrec.id(i);
exit when(l_done);
end loop;
commit;
end;
Thanks for your time Tom.
December 22, 2005 - 12:20 pm UTC
see above.
Correleated subquery update
Thiru, December 22, 2005 - 12:49 pm UTC
The merge statement worked well. Unable to figure out what's the difference between the merge and the update with the union clause !
Also Tom, could you give more pointers to how to get the Correlated subquery update work for the case in question.
Thanks
December 22, 2005 - 5:10 pm UTC
The merge has a different code path and extra checks to make sure a row is updated with a given of values ONCE, the update of a join doesn't have this sort of functionality.
UPDATE (
SELECT A.NUM A_NUM,CK.NUM CK_NUM FROM
(SELECT ID,NUM FROM SM
UNION
SELECT ID,NUM FROM SL) A,CK
WHERE A.ID=CK.ID)
SET CK_NUM=A_NUM
would be:
update ck
set num = (select num
from sm
where id = ck.id
union
select num
from sl
where id = ck.id)
where exists (select num
from sm
where id = ck.id
union
select num
from sl
where id = ck.id)
/
Or this
ops$tkyte@ORA9IR2> select * from t1;
X Y
---------- ----------
1 2
ops$tkyte@ORA9IR2> select * from t2;
X Y
---------- ----------
1 3
1 3
ops$tkyte@ORA9IR2> select * from t3;
no rows selected
ops$tkyte@ORA9IR2> update (select t1.y old_y, (select y from t2 where t2.x = t1.x union select y from t3 where t3.x = t1.x) new_y
2 from t1)
3 set old_y = new_y
4 where new_y is not null;
1 row updated.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from t1;
X Y
---------- ----------
1 3
Not null clause
Thiru, December 23, 2005 - 10:35 am UTC
Thanks for the update query.
Would this be considered as a Correlated update query?
In the example you provided having a table without any rows, is it to show how to get the update with columns that have NULL values?
December 23, 2005 - 12:45 pm UTC
one is a correlated update (the first one) the second is using "scalar subqueries" and looks like a correlated update - but "isn't" technicall speaking.
I meant to have one of the rows from t2 in t3 :) my mistake - same result though.
update statement
A reader, February 24, 2006 - 3:59 pm UTC
Very useful!!!
Update,..
Von, February 27, 2006 - 5:04 pm UTC
Sorry if i am posting this in the wrong place...i have a huge problem with an update statement...
table ord_adj_fct
create table
ord_adj_fct
(
order_number number
,order_line_number number
,order_adj_line_number number
,order_adj_units number
,order_type varchar2(1)
,order_adj_type varchar2(3)
);
there is a primary key on (order_number,order_line_number,order_adj_line_number)
table ord_fct
create table ord_fct
(
order_number number
,order_line_number number
,ord_units number
,ord_retail number
,ord_cost number
,state varchar2(2)
,zip number
,country varchar2(5)
);
there is a primary key on (order_number,order_line_number)
ord_dim has around 75 million rows
ord_fct has around 250 million rows
we are tryin to do a one-time update
declare
cursor c_upd
is
/* the below sql yields 60 million rows and 58 million rows will be updated in ord_fct */
select
order_number,
order_line_number,
sum(order_adj_units) p_units,
from
ord_adj_fct
group by
order_number,
order_line_number;
begin
for r_upd in c_upd
loop
update
ord_fct
set
ord_retail = ord_retail+r_upd.p_units,
ord_cost = ord_cost+r_upd.p_cost
where
order_number = r_upd.order_number and
order_line_number = r_upd.order_line_number;
end loop;
commit;
end;
-- forgive the cursor approach..will bulk update work??
This block doesnt finish at all..
Any ideas to make this run faster...I tried creating a new table like a ctas with the updated columns (outer joining ord_fct to ord_adj_fct) .
.but that didnt finish either.
February 27, 2006 - 5:10 pm UTC
if joining together and doing a CTAS didn't "finish" (it would have), it would lead to a resource issue on your machine, this shouldn't take hours.
I would look to the amount of resources you have on the machine - of which I have no idea. Heck, I don't even know the version after all.
I'd be looking at why the CTAS took so long to do two full scans and a nice big juicy hash join (if a single index was involved, STOP using the RBO)
A reader, February 27, 2006 - 8:14 pm UTC
Tom
thanks for the reply..the version is oracle 9.2.0.7
the sql is
create table ord_fct_tmp as
select
order_number
,order_line_number
,ord_units
,ord_retail+nvl(order_adj_units,0) order_retail
,ord_cost+nvl(order_adj_units,0) order_cost
,state
,zip
,country
from ods.ord_fct a,
(select
order_number
,order_line_number
sum (order_adj_units) order_adj_units
from ods.ods_adj_fct
group by
order_number
,order_line_number) b
where
a.order_number = b.order_number(+) and
a.order_line_number = b.order_line_number(+)
...
plan is
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
CREATE TABLE STATEMENT Optimizer Mode=CHOOSE 250 M 1979800
LOAD AS SELECT
HASH JOIN OUTER 250 M 32G 1979800
TABLE ACCESS FULL ODS.ORD_FCT 250 M 21G 83858
VIEW 75 M 3G 199769
SORT GROUP BY 75 M 975 M 199769
TABLE ACCESS FULL ODS.ORD_ADJ_FCT 75 M 928 M 17080
Do you see anything weird?
Thanks
February 27, 2006 - 8:50 pm UTC
Looks great.
I'd make sure your pga_aggregate_target was set sufficiently, or that you used manual memory mgmt with a large hash area.
(it would not take hours for that on my laptop even, unless you squashed the memory it was allowed to use to "small")
Insert into third table...
SAF, April 26, 2006 - 5:53 am UTC
Hi,
My question is basically this: How can I insert records into a table based on joins from two other tables? Is it possible to do this from one statement?
If you look at the example below, basically I want to insert into tac_attributes from tac_phonemodel and capabilities_profile. The tacs are picked up from the tac_phonemodel and the attributes and values from the capabilities_profile. The joins are done based on model_name and brand_name.
I tried the query in the last INSERT statement, but it returns erroneous results.
Any help will be much appreciated!
CREATE TABLE tac_phonemodel
(tac VARCHAR2(8) PRIMARY KEY,
useragent VARCHAR2(128) ,
phonemaker VARCHAR2(32) NOT NULL,
phonemodel VARCHAR2(256) NOT NULL);
CREATE TABLE capabilities_profile (
deviceId VARCHAR2(255) NOT NULL,
attribute VARCHAR2(255) NOT NULL,
value VARCHAR2(255) NOT NULL);
ALTER TABLE capabilities_profile ADD CONSTRAINT
capabilities_profile_PK PRIMARY KEY (deviceId, attribute);
CREATE TABLE tac_attributes (
tac VARCHAR2(8) NOT NULL,
attribute VARCHAR2(128) NOT NULL,
value VARCHAR2(128) NOT NULL,
units VARCHAR2(64));
ALTER TABLE tac_attributes ADD CONSTRAINT
tac_attributes_PK1 PRIMARY KEY (tac, attribute);
INSERT INTO tac_phonemodel values ('35209600', '', 'Motorola', 'T720');
INSERT INTO tac_phonemodel values ('35249500', '', 'Motorola', 'T720');
INSERT INTO tac_phonemodel values ('35450300', '', 'Motorola', 'A835');
INSERT INTO tac_phonemodel values ('350007', '', 'Nokia', 'N5110i');
INSERT INTO capabilities_profile VALUES ('mot_t720_ver1_subg051100i','flash_lite','false');
INSERT INTO capabilities_profile VALUES ('mot_t720_ver1_subg051100i','doja_2_2','false');
INSERT INTO capabilities_profile VALUES ('mot_t720_ver1_subg051100i','post_method_support','true');
INSERT INTO capabilities_profile VALUES ('mot_t720_ver1_subg051100i','wmlscript_1_1','true');
INSERT INTO capabilities_profile VALUES ('mot_t720_ver1_subg051100i','picture_max_width','120');
INSERT INTO capabilities_profile VALUES ('mot_t720_ver1_subg051100i','wallpaper_preferred_max_width','160');
INSERT INTO capabilities_profile VALUES ('mot_t720_ver1_subg051100i','ringtone_voices','1');
INSERT INTO capabilities_profile VALUES ('mot_t720_ver1_subg051100i','model_name','T720');
INSERT INTO capabilities_profile VALUES ('mot_t720_ver1_subg051100i','doja_2_1','false');
INSERT INTO capabilities_profile VALUES ('mot_t720_ver1_subg051100i','mms_vcard','false');
INSERT INTO capabilities_profile VALUES ('mot_t720_ver1_subg051100i','brand_name','Motorola');
INSERT INTO capabilities_profile VALUES ('mot_a835_ver1_sub701235','flash_lite','false');
INSERT INTO capabilities_profile VALUES ('mot_a835_ver1_sub701235','doja_2_2','false');
INSERT INTO capabilities_profile VALUES ('mot_a835_ver1_sub701235','post_method_support','true');
INSERT INTO capabilities_profile VALUES ('mot_a835_ver1_sub701235','wmlscript_1_1','true');
INSERT INTO capabilities_profile VALUES ('mot_a835_ver1_sub701235','picture_max_width','0');
INSERT INTO capabilities_profile VALUES ('mot_a835_ver1_sub701235','wallpaper_preferred_max_width','0');
INSERT INTO capabilities_profile VALUES ('mot_a835_ver1_sub701235','ringtone_voices','1');
INSERT INTO capabilities_profile VALUES ('mot_a835_ver1_sub701235','model_name','A835');
INSERT INTO capabilities_profile VALUES ('mot_a835_ver1_sub701235','doja_2_1','false');
INSERT INTO capabilities_profile VALUES ('mot_a835_ver1_sub701235','mms_vcard','false');
INSERT INTO capabilities_profile VALUES ('mot_a835_ver1_sub701235','columns','14');
INSERT INTO capabilities_profile VALUES ('mot_a835_ver1_sub701235','j2me_softkeys','2');
INSERT INTO capabilities_profile VALUES ('mot_a835_ver1_sub701235','download_limit','30');
INSERT INTO capabilities_profile VALUES ('mot_a835_ver1_sub701235','picture_greyscale','false');
INSERT INTO capabilities_profile VALUES ('mot_a835_ver1_sub701235','midp_20','false');
INSERT INTO capabilities_profile VALUES ('mot_a835_ver1_sub701235','brand_name','Motorola');
INSERT INTO capabilities_profile VALUES ('mot_a835_ver1_sub701235','table_support','true');
COMMIT;
INSERT INTO tac_attributes (tac, attribute, value)
SELECT tp.tac, cp.attribute, cp.value
FROM capabilities_profile cp, tac_phonemodel tp
WHERE tp.tac IN
(SELECT tac FROM capabilities_profile cp, tac_phonemodel tp
WHERE cp.attribute = 'brand_name'
AND cp.value = tp.phonemaker
INTERSECT
SELECT tac FROM capabilities_profile cp, tac_phonemodel tp
WHERE cp.attribute = 'model_name'
AND cp.value = tp.phonemodel)
AND cp.deviceId IN
(SELECT deviceId FROM capabilities_profile cp, tac_phonemodel tp
WHERE cp.attribute = 'brand_name'
AND cp.value = tp.phonemaker
INTERSECT
SELECT deviceId FROM capabilities_profile cp, tac_phonemodel tp
WHERE cp.attribute = 'model_name'
AND cp.value = tp.phonemodel);
April 26, 2006 - 8:14 am UTC
I'm sorry your query is incorrect, but it would appear you already know how to insert data from two joined tables? It is inserting the data, you just haven't written the query yet that returns the data you want.
Normally, a join would have
where table1.key = table2.key
not as you have coded. What you have done is:
joined every row in CP to every row in TP
kept those that had tac in (some set) and deviceId in (some set)
I cannot really tell from your existing query what your goal was - sorry.
Troubles
Cotton, July 12, 2006 - 7:32 am UTC
Hello Tom. I have the following trouble.
I try to execute the query:
update /* +BYPASS_UJVC*/
(select t.WD_COMM t1_WD_COMM, t.WD_DATE t1_WD_DATE, t.WD_ID t1_WD_ID, t.WD_TYPE t1_WD_TYPE, t.WD_WTIME t1_WD_WTIME,tt.WD_COMM t2_WD_COMM, tt.WD_DATE t2_WD_DATE, tt.WD_ID t2_WD_ID, tt.WD_TYPE t2_WD_TYPE, tt.WD_WTIME t2_WD_WTIME
from table1 t , table2 tt
where t.WD_ID = tt.WD_ID
and tt.action = 'UPDATE'
and not exists ( select 1
from table2 tab
where tab.WD_ID = tt.WD_ID
and tab.id > tt.id))
set t1_WD_ID = t2_WD_ID,
t1_WD_COMM = t2_WD_COMM,
t1_WD_DATE = t2_WD_DATE,
t1_WD_TYPE = t2_WD_TYPE,
t1_WD_WTIME = t2_WD_WTIME
i have the error:
ORA-01779: cannot modify a column which maps to a non-preserved table
When i execute select subquery i recieve 1 row with ID e.g. 1000. Each of the tables table1 and table2 contains only the one row with ID 1000. Why the error ORA-01779 appears?
July 12, 2006 - 4:43 pm UTC
Not going to touch that hint, stop using it (thankfully you used it wrong!!!! perfect, much safer that way)
what version do you have
and please supply table creates.
I'll show you how to do this legit - using documented features only.
Update with an inline view
Jamu, July 19, 2006 - 10:51 pm UTC
Tom,
I am stuck with one update. Can you please suggest a method to do.
create table t ( a number, b number, c number , d varchar2(3),e number primary key);
begin
insert into t values ( NULL,3,30,'ab',1);
insert into t values ( NULL,3,10,'ab',2);
insert into t values ( NULL,3,10,'ab',3);
insert into t values ( NULL,1,20,'ab',4);
insert into t values ( NULL,3,20,'bc',5);
insert into t values ( NULL,3,10,'bc',6);
insert into t values ( NULL,3,10,'bc',7);
insert into t values ( NULL,1,20,'bc',8);
end;
select * from t
/ a b c d e
-----------------------------------------
3 30 ab 1
3 10 ab 2
3 10 ab 3
1 20 ab 4
3 20 bc 5
3 10 bc 6
3 10 bc 7
1 20 bc 8
I would like to get the result where col a will have the difference between col c and ( Sum(col c) where b=3 group by d) and only for rows that have b=1.
So the net result should look like:
a b c d e
-----------------------------------------
3 30 ab 1
3 10 ab 2
3 10 ab 3
30 1 20 ab 4
3 20 bc 5
3 10 bc 6
3 10 bc 7
20 1 20 bc 8
July 22, 2006 - 4:31 pm UTC
sorry but that "output" is totally garbled - not sure what you mean.
Michel Cadot, July 23, 2006 - 2:00 am UTC
You example does not show "sum" but "max".
Moreover, do you really want the total sum (of overall max) of for just the rows between two b=1 (ordering the rows by e)?
What happens if there are other values than 1 and 3 for b?
And if d changes before b changes from 3 to 1?
And...
Your specifications are quite imprecise.
July 23, 2006 - 9:32 am UTC
...
Your specifications are quite imprecise.
.....
welcome to my world :)
This, more than anything, is what I have a hard time with - imprecise, ambigous or just plain "missing" specifications....
Update requirement
Andy, July 24, 2006 - 10:10 am UTC
I think Jamu's requirement is something like this:
update t t1
set t1.a = (select sum(t2.c) from t t2 where t2.b = 3 and t2.d = t1.d) - t1.c
where t1.b = 1
July 24, 2006 - 11:00 am UTC
I'd rather have Jamu be a tad more specific before I take a guess :)
NC, July 27, 2006 - 12:06 pm UTC
Tom,
When I update the table, the consistent gets is very high, how do I reduce it? Here is the sample statement:
Update t
set col1=:1,col2=:2,col3=:4...
where primary_key_col=:x;
Table doesn't have any partitions. And there is only one index on the table.
Thanks.
July 27, 2006 - 12:22 pm UTC
define "very high" and give an example.
A reader, July 27, 2006 - 12:47 pm UTC
Thanks for the response Tom!
Here are the statistics:
No. of executions:253
Consistent gets:25798
Rows processed:253
BufferGets/Execution:102
Is there a way I can reduce the consistent gets?
Thanks.
July 27, 2006 - 1:56 pm UTC
*need full example*
indexes affect this
other concurrent users affect this (multi-versioning, we might have to roll back the changes)
so, set up the example so we can see the width of the table, number of indexes and so on.
A reader, July 27, 2006 - 1:47 pm UTC
Table has a CLOB column, is that the issue?
July 27, 2006 - 2:12 pm UTC
need example, not going to get anywhere without one.
do you update the clob - that'll be material as well.
UPDATE and NULL-values, an explanation?
Duke Ganote, October 20, 2006 - 11:46 am UTC
Although it seems counter-intuitive to get null values from some updates (like the original example), I think it's semantically like a scalar subquery:
create table src ( x number primary key, y number );
create table tgt ( x number primary key, y number );
insert into tgt values ( 1, 1 );
insert into tgt values ( 2, 2 );
insert into tgt values ( 3, 3 );
insert into src values ( 3, 33 );
insert into src values ( 2, 22 );
SQL> select * from src;
X Y
---------- ----------
3 33
2 22
SQL> select * from tgt;
X Y
---------- ----------
1 1
2 2
3 3
SQL> ed
Wrote file afiedt.buf
1 select x
2 , ( select src.y from src where src.x = tgt.x) as Y
3* from tgt
SQL> /
X Y
---------- ----------
1
2 22
3 33
SQL> update tgt set tgt.y = ( select src.y from src where src.x = tgt.x );
3 rows updated.
SQL> select * from tgt;
X Y
---------- ----------
1
2 22
3 33
October 20, 2006 - 12:23 pm UTC
not sure what point you are trying to make?
but a subquery used that way in an update returns 0 or 1 rows. but unlike a scalar subquery, can return many columns:
ops$tkyte%ORA10GR2> create table t ( x int, y int );
Table created.
ops$tkyte%ORA10GR2> insert into t values ( 1, 1 );
1 row created.
ops$tkyte%ORA10GR2> update t set (x,y) = (select 2,3 from dual);
1 row updated.
ops$tkyte%ORA10GR2> select * from t;
X Y
---------- ----------
2 3
My point, and I think I have one... :)
Duke Ganote, October 20, 2006 - 1:05 pm UTC
I'm just attempting to grasp WHY the original query set some values to null. It's entirely counter-intuitive to users of other DBMSs
http://database.ittoolbox.com/groups/technical-functional/sql-l/sql-syntax-help-update-table-with-data-from-ano-ther-table-1213977 To "preview" the results of the UPDATE, I note a SELECT with scalar subquery (bolded in both below) produces the analogous results:
1 select x
2 , <b>( select src.y from src where src.x = tgt.x)</b> as Y
3* from tgt
SQL> /
X Y
---------- ----------
1
2 22
3 33
SQL> update tgt set tgt.y = <b>( select src.y from src where src.x = tgt.x )</b>;
3 rows updated.
SQL> select * from tgt;
X Y
---------- ----------
1
2 22
3 33
October 20, 2006 - 1:22 pm UTC
oh, and ANSI says this is the way it works too...
best straight forward example
ongun, November 08, 2006 - 11:21 am UTC
thanks Tom, your answer is really fast, efficient and straight forward and easy to implement.
Merge on null column?
Stephan, December 09, 2006 - 10:35 pm UTC
create table t1 (
id number,
name varchar2(20),
txt varchar2(20)
);
insert into t1 values (1, 'first', 'first object');
insert into t1 values (1, 'second', 'second object');
insert into t1 values (2, 'first', 'first object');
insert into t1 values (2, 'second', 'second object');
insert into t1 values (null, 'first', 'first object');
insert into t1 values (null, 'second', 'second object');
commit;
create table t2 as select * from t1;
insert into t2 values (1,'third','third object');
insert into t2 values (2, 'third', 'third object');
insert into t2 values (null, 'third', 'third object');
commit;
Let's say t2 is our reference table and t1 is actually missing those other three rows. I want to insert any missing rows into t1. If I try merging like this,
merge into t1
using t2
on (t2.id=t1.id and t2.name=t1.name)
when matched then update set t1.txt=t2.txt
when not matched then insert (id, name, txt)
values (t2.id, t2.name, t2.txt);
I get 11 rows in t1, not the expected 9:
ID NAME TXT
---------- -------------------- --------------------
1 first first object
2 second second object
2 first first object
1 second second object
second second object
first first object
first first object
second second object
1 third third object
third third object
2 third third object
I'm presuming the problem is using the null value in my merge statement - but how else can I merge accurately?
December 10, 2006 - 7:35 pm UTC
ops$tkyte%ORA10GR2> merge into t1
2 using t2
3 on ( (t2.id=t1.id or (t2.id is null and t1.id is null))
4 and
5 (t2.name=t1.name or (t2.name is null and t1.name is null)) )
6 when matched then update set t1.txt=t2.txt
7 when not matched then insert (id, name, txt)
8 values (t2.id, t2.name, t2.txt);
9 rows merged.
ops$tkyte%ORA10GR2> select * from t1;
ID Tablespace Name TXT
---------- ---------------------- --------------------
1 first first object
1 second second object
2 first first object
2 second second object
first first object
second second object
1 third third object
2 third third object
third third object
9 rows selected.
not sure I'd call it "fast" in general...
nulls are not equal to null
nulls are not NOT equal to null
Slightly more
Stephan, December 10, 2006 - 7:44 pm UTC
Sorry, I should have mentioned that I'm running 9.2.0.4.0. Also, I do understand that nulls are neither equal nor not equal to nulls - I understand why a straight merge isn't working. But is there something I can use in its place?
Thanks,
Stephan
December 10, 2006 - 8:01 pm UTC
did you try the above?
it is just a join - you need to specifically do the null checking.
Perfect
Stephan, December 10, 2006 - 7:47 pm UTC
You must have got that up while I was trying to add more info. Thanks much.
December 10, 2006 - 8:02 pm UTC
ahh, and I commented right above whilst you were doing this one :)
Thank you, but clarify
Stephan, December 23, 2006 - 10:36 pm UTC
That did exactly what I was looking for. I was wondering, however, what you meant by not sure you'd call it "fast" - do you mean this is an inefficient way to do it?
I'm looking at an application upgrade issue where some script failed - some values were not inserted into a table. Because the target table could have values that have been changed, I can't just truncate it or drop it to replace it from the reference table. I'm only looking to insert the missing rows. Those two columns, one which can and does have null values, and the other, which does not, serve as a psuedo-primary key. If it was just one column that provided our PK (or pseudo-PK), I would probably do something along the lines of:
insert into t1 (id, name, txt) select * from t2 where id in (select id from t2 minus select id from t1);
I'm not sure that's the most efficient way either. I can probably get away with it, since the tables in question have under 1000 rows each. But, given these constraints, is there a better way to do it?
So, for either
December 24, 2006 - 9:17 am UTC
...
2 using t2
3 on ( (t2.id=t1.id or (t2.id is null and t1.id is null))
4 and
5 (t2.name=t1.name or (t2.name is null and t1.name is null)) )
....
is not in general something that will be "really efficient and fast" - on large sets.
Updating Review
M.P.Manna, December 30, 2006 - 10:15 am UTC
When updating a column with an update-statement, the value of some records
(records that don't need to be updated), are changed into the value NULL. I use
the next statement:
update table name B
set columnname =
( select value
from lookup O
where B.keyname = O.keyname
and O.Othercolumn = Other_value);
As a result all the necessary changes are made, but also the records that don't
need to be updated: they get the Null-value. Is there a way of avoiding this,
because we do need to update the records frequently, but not all records at the
same time.
Is there a kind of workaround we can use for updating the records that need to
be updated without changing the other records too with a Null value?
Thanks and HAPPY NEW YEAR
M.P.Manna
December 30, 2006 - 11:17 am UTC
umm, why did you cut and paste the original question?
did you not see there was already an answer?
Su Baba, January 10, 2007 - 1:15 pm UTC
CREATE TABLE parent AS
SELECT MOD(object_id, 300) parent_id
FROM all_objects;
CREATE TABLE child AS
SELECT object_id child_id, null parent_id
FROM all_objects;
ALTER TABLE parent ADD CONSTRAINT parent_pk PRIMARY KEY (parent_id);
ALTER TABLE child ADD CONSTRAINT child_pk PRIMARY KEY (child_id);
Now I want to randomly assign a parent_id to the child table. I also want to make sure that every parent has at least one child.
What's the most efficient way to accomplish this?
update a column in one tabel with random row of a cloumn in another table
Nengbing, January 18, 2007 - 2:53 pm UTC
Hi,
Your SQL for creating tables and constraints need to be modified to work. For example, add "distinct" for PARENT table; change NULL to 111111111 for CHILD table.
It seems to me if you need randomly assign a PARENT_ID to a CHILD_ID, you may not gurantee all PARENT_IDs are used.
Here is what I tried:
CREATE TABLE parent AS
SELECT distinct MOD(object_id, 300) parent_id
FROM all_objects;
CREATE TABLE child AS
SELECT object_id child_id, 1111 parent_id
FROM all_objects where object_id < 9999;
update child t
set parent_id= (
select parent_id from (
select s1.child_id,s2.parent_id from
(select parent_id,rownum rn2 from parent) s2
join ( select child_id,
trunc(dbms_random.value(1,(select count(*) from parent)),0) rn1 from child) s1
on (s2.rn2=s1.rn1)
) s
where s.child_id=t.child_id
)
But some PARENT_ID is missing.
"Reverse" column values
Nengbing, January 18, 2007 - 3:10 pm UTC
Hi, Tom,
I want to update pos2 in a group (team) with the "reverse" of the position (pos1). For example, if the minimum of pos1 is 0 and maximum of pos is 10, I'd want the row for minimum of pos1 (0) to have maximum pos2 of 10. Could you please help explain why the following SQL did not work?
drop table t1 purge;
create table t1 (
name varchar2(5),
team varchar2(2),
pos1 number,
pos2 number
);
insert into t1 values ('id01','10',0,NULL);
insert into t1 values ('id02','10',.2,NULL);
insert into t1 values ('id04','10',.7,NULL);
insert into t1 values ('id05','10',2.7,NULL);
insert into t1 values ('id06','10',6,NULL);
insert into t1 values ('id08','19',.7,NULL);
insert into t1 values ('id13','10',5,NULL);
insert into t1 values ('id09','19',.9,NULL);
insert into t1 values ('id10','19',1.1,NULL);
insert into t1 values ('id11','19',2.6,NULL);
insert into t1 values ('id12','19',4.2,NULL);
insert into t1 values ('id14','10',5,NULL);
insert into t1 values ('id03','10',.5,NULL);
insert into t1 values ('id07','19',0,NULL);
update t1 t
set pos2= (
select distinct max(pos1) over (partition by team) - pos1
from t1 s
where s.team=t.team and s.pos1=t.pos1
)
where exists (select team,pos1 from t1 s
where s.team=t.team and s.pos1=t.pos1)
and team in ('10') ;
select * from t1;
NAME TE POS1 POS2
----- -- ---------- ----------
id01 10 0 0
id02 10 .2 0
id04 10 .7 0
id05 10 2.7 0
id06 10 6 0
id08 19 .7
id13 10 5 0
id09 19 .9
id10 19 1.1
id11 19 2.6
id12 19 4.2
id14 10 5 0
id03 10 .5 0
id07 19 0
14 rows selected.
update two tables
wawan, March 12, 2007 - 5:00 am UTC
Tom,
I copied from previus thread and using your example above
scott@ORA734.WORLD> update name
2 set columnName = ( select value
3 from lookup
4 where lookup.keyname = name.keyname
5 and otherColumn = :other_value )
6 where exists ( select value
7 from lookup
8 where lookup.keyname = name.keyname
9 and otherColumn = :other_value )
10 /
1 row updated.
my question is, suppose table Lookup have column STATUS,
how to update this column to 'Y' for rows that
has been used (to update the NAME table) ?
so I dont need to do
SQL> update LOOKUP set STATUS=Y
where exists ( select value
from lookup
where lookup.keyname = name.keyname);
Is it possible do in single query only ?
March 12, 2007 - 7:52 pm UTC
you update lookup and query lookup?
i think you mean:
update lookup set status = 'Y' where keyname in (select value from name);
update 2 tables
wawan, March 12, 2007 - 9:01 pm UTC
You're right TOM,
can we update two tables (NAME and LOOKUP)
in one script only ?
so I dont need to type :
"update lookup set status = 'Y' where keyname in (select value from name); "
March 13, 2007 - 11:18 am UTC
eh? this doesn't compute with me. not at all sure what you mean.
wawan, March 13, 2007 - 10:31 pm UTC
sorry for my bad english.
maybe the question is :
how to update two tables at onces ?
in this case, update NAME tables, and then update
LOOKUP tables it self.
I wanto join these 2 sql to 1 sql.
SQL> update name
set columnName = ( select value
from lookup
where lookup.keyname = name.keyname
and otherColumn = :other_value )
where exists ( select value
from lookup
where lookup.keyname = name.keyname
and otherColumn = :other_value );
and
SQL> update lookup set status = 'Y' where keyname in (select value from name);
March 14, 2007 - 7:36 am UTC
you cannot, an update statement can only update a single table.
Update in a single statement
A reader, May 10, 2007 - 9:25 am UTC
HI Tom,
I have the following table
create table t1 (ide number, x1 varchar2(3), x2 varchar2(6), x3 varchar2(6), x4 varchar2(6), x5 varchar2(8))
> desc t1
Name Null? Type
------------------------------- -------- ----
IDE NUMBER
X1 VARCHAR2(3)
X2 VARCHAR2(6)
X3 VARCHAR2(6)
X4 VARCHAR2(6)
X5 VARCHAR2(8)
I would like to do the following update
update t1
set x1 = (select x1 - 0.01*x1 * x5 from t1 where x4 = 'Y')
where x5 is not null;
I didn't succeed to do it via the above simple sql. I did it via a plsql block (with cursors)
Could you please advise
Thanks
May 11, 2007 - 10:53 am UTC
why didn't you succeed? can you give us *a clue*
Update
Reader, January 23, 2008 - 9:50 am UTC
CREATE TABLE testupd
(
SYS_PK NUMBER NOT NULL,
id NUMBER NOT NULL,
START_DATE DATE DEFAULT sysdate NOT NULL,
END_DATE DATE DEFAULT '31-dec-9999' NOT NULL,
);
insert into testupd
VALUES(19,577,to_date('11-21-2007','MM-DD-YYYY'),to_Date('12-31-9999','MM-DD-YYYY'));
insert into testupd
VALUES(20,577,'to_date('11-22-2007','MM-DD-YYYY'),to_Date('12-31-9999','MM-DD-YYYY'));
insert into testupd
VALUES(21,578,to_date('11-21-2007','MM-DD-YYYY'),to_Date('12-31-9999','MM-DD-YYYY'));
insert into testupd
VALUES(22,578,to_date('11-22-2007','MM-DD-YYYY'),to_Date('12-31-9999','MM-DD-YYYY'));
insert into testupd
VALUES(23,578,to_date('11-23-2007','MM-DD-YYYY'),to_Date('12-31-9999','MM-DD-YYYY'));
insert into testupd
VALUES(24,579,to_date('11-21-2007','MM-DD-YYYY'),to_Date('12-31-9999','MM-DD-YYYY'));
insert into testupd
VALUES(25,579,to_date('11-22-2007','MM-DD-YYYY'),to_Date('12-31-9999','MM-DD-YYYY'));
I want to update as follows:
data before updating:
SYS_PK ID START_DATE END_DATE
19 577 11/21/2007 12/31/9999
20 577 11/21/2007 12/31/9999
SYS_PK ID START_DATE END_DATE
21 578 11/21/2007 12/31/9999
22 578 11/22/2007 12/31/9999
23 578 11/23/2007 12/31/9999
SYS_PK ID START_DATE END_DATE
24 579 11/21/2007 12/31/9999
25 579 11/22/2007 12/31/9999
If there are two records with same ID:
Enddate of 1st record should be set to start date of second record and end date of 2nd record should be set to 12-31-9999.
If there are three records with same ID:
Enddate of 1st record should be set to start date of second record;
Enddate of 2nd record should be set to startdate of third record;
End date of record 3 should be set to 12-31-9999.
Data should be as below after update:
SYS_PK ID START_DATE END_DATE
19 577 11/21/2007 11/22/2007
20 577 11/22/2007 12/31/9999
SYS_PK ID START_DATE END_DATE
21 578 11/21/2007 11/22/2007
22 578 11/22/2007 11/23/2007
23 578 11/23/2007 12/31/9999
SYS_PK ID START_DATE END_DATE
24 579 11/21/2007 11/22/2007
25 579 11/22/2007 12/31/9999
Can you please let me know how to perform this update?
January 23, 2008 - 2:15 pm UTC
I hate this for two reasons:
END_DATE DATE DEFAULT '31-dec-9999' NOT NULL,
);
a) it demonstrates you never ran the test case for me. that comma says FAIL
b) it demonstrates you are using some whacky default instead of null, sigh, FAIL (you just want to totally screw up the optimizer...)
well, three reasons...
ops$tkyte%ORA10GR2> select * from testupd order by id, start_date;
SYS_PK ID START_DAT END_DATE
---------- ---------- --------- ---------
19 577 21-NOV-07 31-DEC-99
21 578 21-NOV-07 31-DEC-99
22 578 22-NOV-07 31-DEC-99
23 578 23-NOV-07 31-DEC-99
24 579 21-NOV-07 31-DEC-99
25 579 22-NOV-07 31-DEC-99
6 rows selected.
hmmm, oh, your inserts FAIL too.....
ok, fixing that...
ops$tkyte%ORA10GR2> select sys_pk, id, start_date, end_date, lead(start_date) over (partition by id order by start_date) next_start_date
2 from testupd
3 order by id, start_date
4 /
SYS_PK ID START_DAT END_DATE NEXT_STAR
---------- ---------- --------- --------- ---------
19 577 21-NOV-07 31-DEC-99 22-NOV-07
20 577 22-NOV-07 31-DEC-99
21 578 21-NOV-07 31-DEC-99 22-NOV-07
22 578 22-NOV-07 31-DEC-99 23-NOV-07
23 578 23-NOV-07 31-DEC-99
24 579 21-NOV-07 31-DEC-99 22-NOV-07
25 579 22-NOV-07 31-DEC-99
7 rows selected.
ops$tkyte%ORA10GR2> merge into testupd
2 using (
3 select *
4 from (
5 select rowid rid, lead(start_date) over (partition by id order by start_date) next_start_date
6 from testupd
7 )
8 where next_start_date is not null
9 ) X
10 on (testupd.rowid = x.rid)
11 when matched then update set end_date = next_start_date
12 /
4 rows merged.
ops$tkyte%ORA10GR2> select * from testupd order by id, start_date;
SYS_PK ID START_DAT END_DATE
---------- ---------- --------- ---------
19 577 21-NOV-07 22-NOV-07
20 577 22-NOV-07 31-DEC-99
21 578 21-NOV-07 22-NOV-07
22 578 22-NOV-07 23-NOV-07
23 578 23-NOV-07 31-DEC-99
24 579 21-NOV-07 22-NOV-07
25 579 22-NOV-07 31-DEC-99
7 rows selected.
very nice
Sokrates, January 23, 2008 - 2:23 pm UTC
especially the initial hate-clauses
Reader, January 23, 2008 - 3:15 pm UTC
Thank You. Appologies for the mistake while sending the scipts.
Reader, January 25, 2008 - 8:55 am UTC
Tom,
You mentioned that, using default date instead of the null will screw up the optimizer. Can you please explain how it causes damage to the optimizer?
Thanks
January 25, 2008 - 9:27 am UTC
you have dates.
they have a low bound and a high bound. Let's say they are "date of hire"
high bound is easy - it is "today" practically.
but you decide to use 31-dec-9999 for "unknowns"
all of a sudden, the hi/lo values instead of being say a 20 year spread - becomes a really wide spread.
Histograms get messed up (because the nulls would not impact them)
Cardinality estimates get impacted.
ops$tkyte%ORA10GR2> create table t
2 as
3 select created hiredate
4 from all_objects;
Table created.
ops$tkyte%ORA10GR2> alter table t modify hiredate null;
Table altered.
ops$tkyte%ORA10GR2> insert into t
2 select null
3 from all_users;
39 rows created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T' );
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> @at
ops$tkyte%ORA10GR2> column PLAN_TABLE_OUTPUT format a72 truncate
ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> select * from t where hiredate between to_date( '01-jan-2007' ) and to_date( '15-jan-2007' );
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 857 | 5999 | 37 (11)| 00:00:01
|* 1 | TABLE ACCESS FULL| T | 857 | 5999 | 37 (11)| 00:00:01
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("HIREDATE">=TO_DATE('2007-01-01 00:00:00', 'yyyy-mm-dd
hh24:mi:ss') AND "HIREDATE"<=TO_DATE('2007-01-15 00:00:00'
hh24:mi:ss'))
ops$tkyte%ORA10GR2> set autotrace off
ops$tkyte%ORA10GR2> update t set hiredate = to_date( '31-dec-9999' ) where hiredate is null;
39 rows updated.
ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T' );
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> @at
ops$tkyte%ORA10GR2> column PLAN_TABLE_OUTPUT format a72 truncate
ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> select * from t where hiredate between to_date( '01-jan-2007' ) and to_date( '15-jan-2007' );
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 66 | 528 | 36 (9)| 00:00:01
|* 1 | TABLE ACCESS FULL| T | 66 | 528 | 36 (9)| 00:00:01
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("HIREDATE"<=TO_DATE('2007-01-15 00:00:00', 'yyyy-mm-dd
hh24:mi:ss') AND "HIREDATE">=TO_DATE('2007-01-01 00:00:00'
hh24:mi:ss'))
ops$tkyte%ORA10GR2> set autotrace off
39 nulls - turned into a wacky date - and look at the card= estimates, how they changed....
Reader, January 25, 2008 - 10:19 am UTC
Tom,
I am new to tuning. Can you please pass the link to find what card= estimates means?
Also in the below plans, where do I see the card=estimtes?
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 857 | 5999 | 37 (11)| 00:00:01
|* 1 | TABLE ACCESS FULL| T | 857 | 5999 | 37 (11)| 00:00:01
------------------------------------------------------------------------
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 66 | 528 | 36 (9)| 00:00:01
|* 1 | TABLE ACCESS FULL| T | 66 | 528 | 36 (9)| 00:00:01
------------------------------------------------------------------------
January 25, 2008 - 10:59 am UTC
card = is the estimated cardinality, how many rows the optimizer expected to get.
See the ROWS, that is "cardinality"
without NULLS messing up the distribution of the data, the optimizer thought it would get 857 rows (cardinality of 857).
after putting in the made up values, it thinks the cardinality will be 66 instead - but note how turning those 39 nulls into 31-dec-9999 could NOT have affected the answer at all - it queried 2007 data.
You mess with the end points of data, statistics, and you messed the data.
Reader, January 25, 2008 - 11:36 am UTC
Tom,
you said:
after putting in the made up values, it thinks the cardinality will be 66 instead - but note how turning those 39 nulls into 31-dec-9999
could NOT have affected the answer at all - it queried 2007 data.
After putting the date as 31-dec-9999 in place of nulls, the optimizer is assuming to get 66 rows instead of 857. Is my understanding correct?
Optimizer is assuming to get less records (66) when the date=31-dec-9999 than when the date is null (857). How will this effect performance?
What happens to the cardinality, if I issue the below query instead of getting the 2007 data
select * from t where hiredate = '31-dec-9999';
you said-
You mess with the end points of data, statistics, and you messed the data.
Can you please elaborate on this one or pass me some link where I can get more information?
I did not understand - what end points of data means.
Thanks
January 28, 2008 - 6:41 am UTC
... After putting the date as 31-dec-9999 in place of nulls, the optimizer is
assuming to get 66 rows instead of 857. Is my understanding correct? ...
yes, that was the crux of the example
... Optimizer is assuming to get less records (66) when the date=31-dec-9999 than
when the date is null (857). How will this effect performance?...
the optimizer is a mathematical model, one of the largest contributors to the cost of a query is the estimated cardinalities (row counts).
If you get the wrong cardinality, you are more likely to develop the wrong plan.
If you think "oh, I'll get most of the table", it'll full scan - but what if it was only going to get 1 out of a 1,000,000 rows - then it should have used the index.
wrong cardinality => wrong plan => poor performance
You don't understand what end points mean?
You went from having end points for hiredate of X and Y (with 39 nulls - unknown) and y was less than "today" to end points of X and 31-dec-9999 - where 31-dec-9999 is far far far into the future.
the end points are the high and low values. The optimizer will use them to figure out "how many values in general fall into a range"
If I told you that
you have a room with 110 people, 100 we know are ages 21-30 - 10 we are not sure on, you might guess that about 10 people are 21, 10 are 22, 10 are 23 and so on.
If I change that to you have a room with 110 people ages ranging from 21-99,999 - now what might be your guess?
Update from another table over Database link
Maverick, February 28, 2008 - 1:22 pm UTC
Tom, I was trying to Update a table in my database using another table as source [which is in another database] over a database link. Both the tables source/destination are having primary keys and I made sure for the join I am giving, it returns only one row.
Update (select a.col1,b.col3
from table1 a,
table2@dblink b
where a.col2=b.col2)
set col1=col3;
Now it still gives me this error:
"ORA-01779: cannot modify a column which maps to a non key-preserved table"
So, I changed the source from this table to a Materialized view [based on this source table] and ran my update again, and it updated few rows.
My questions is , Does Oracle has a problem with Updates over database link and Creating Materialized view is the only solution?
Update over DBLink
Maverick, April 14, 2008 - 2:14 pm UTC
Tom, I guess you never got a chance to see above question [Update over a dblink]. Do you see any problem doing this kind of Updates over a dblink?
Thanks,
April 16, 2008 - 2:06 pm UTC
you can use a correlated update instead.
update t1 set c = (select ... from t2 where ...)
where exists (select ... from t2 where ...)
Update Using Row ID
Bhushan, May 10, 2008 - 12:20 am UTC
Hello Tom,
Need your Opinion.Is it ok to update based on Rowid's
For eg: Package Specification has a cursor declared
select * from temp_invoice;
and a procedure named proc_main;
Proc_main calls a procedure proc_update which updates some columns in temp_invoice based on business logic.
The curosr is opened in proc_main, now instead of passing all the columns to proc_update from proc_main, i pass the rowid; fetch the columns values from temp_invoice table do the calculations and update temp_invoice.
I remember reading somewhere on your site that you do not prefer using rowid for updates as ORacle does not recommend it.
Loking forward for your reply.
Cheers!!!
Bhushan
May 12, 2008 - 12:13 pm UTC
don't be lazy
pass the inputs, come on. Really.
Why hugely increase the work the database has to perform because you do not feel like passing formal parameters? Please.
and think about how you can do this with LESS SQL - avoid this slow by slow habit, don't think row by row, think SETS
I asked your Opinion!!!
Bhushan, May 12, 2008 - 12:26 pm UTC
One Row in the table correspoonds to ONE UNIQUE invoice number. So thinking SETS wont help here.We have to process ROW by ROW
Yes i agree that passing rowid's and then fetching the same values which had been fetched in the cursor of the Parent Procedure is a overhead.But just wanted to know if it's OK to use the Row ID.Does not mean i will!!!
Regards,
Bhushan
May 12, 2008 - 1:59 pm UTC
umm, yes, thinking in sets helps.
I don't understand what "One Row in the table correspoonds to ONE UNIQUE invoice number. So thinking SETS wont help here."
has to do with anything. That is a meaningless statement. So what, you have a 1:1 relationship (just join).
you have code you are going to write, it is going to process row by row. If you can do it row by row, you can likely do it set wise as well.
Sets Example please.
Bhushan, May 12, 2008 - 2:29 pm UTC
Well,
We get the invoice details from one of our legacy Systems.
It is fed to a DW.Here we update the Country,region the CM/DM details, exchange rates and much more information so that this inturn goes to the application in the Format and the country region codes they can identify.Well this is a very very high level picture of how the data is processed,So the current code that is already present runs this cursor on the stg table, updates the information for a Customer (1st row) then the second and so on and at the end the data frm staging table is moved to the main table.
Now, please explain what do you mean by this can be done in sets.Do you imply that we should (can or cannot can be thought later) update this information for all the customers at one go ..? Is that what sets are you referring to?.
If not...request you to explain.
Regards,
Bhushan
May 13, 2008 - 10:00 am UTC
... .Do you imply
that we should (can or cannot can be thought later) update this information for
all the customers at one go ..? ...
bingo - now you are thinking in SETS.
if you just need to update existing records with your temp data
update (select .... from your_temp_table, your_perm_table where join... )
set ......;
if you need to update/delete/insert your existing records with your temp data, read all about MERGE
A reader, June 27, 2008 - 7:25 pm UTC
Please see the two update statements below. I was expecting both updates to return 0 rows. Why did the first update return so many records?
CREATE TABLE x AS
SELECT object_id, status
FROM all_objects;
ALTER TABLE x ADD PRIMARY KEY (object_id);
CREATE TABLE y AS
SELECT object_id, status
FROM all_objects
WHERE 1 = 2;
ALTER TABLE y ADD PRIMARY KEY (object_id);
UPDATE x
SET status = (SELECT status
FROM y
WHERE x.object_id = y.object_id);
38999 rows updated.
rollback;
UPDATE (
SELECT x.status x_status, y.status y_status
FROM x, y
WHERE x.object_id = y.object_id
)
SET x_status = y_status;
0 rows updated.
June 28, 2008 - 1:31 pm UTC
well, look at them, one of them updates every row-
UPDATE x
SET status = (SELECT status
FROM y
WHERE x.object_id = y.object_id);
(do you see a where clause there, that is conceptually the same as)
UPDATE x SET status = f(x.object_id)
your subquery is just a function!! you updated EVERY ROW, you asked for EVERY ROW to be updated.
whereas you updated a join:
UPDATE (
SELECT x.status x_status, y.status y_status
FROM x, y
WHERE x.object_id = y.object_id
)
SET x_status = y_status;
you said "find the rows in X and Y that join, then update them - and change the rows in X to something we found in Y
Let me turn this around, can you explain why you believe they should have operated the same? Instead of me trying to explain what appears obvious to me - you tell us why you thought they should be the same and we can address that for you?
A reader, June 28, 2008 - 7:51 pm UTC
The result of the first update statement is not clear to me. The way I read the statement is this
"For every record in table x, if there is a matching record in table y, update the status of table x to that of table y."
July 01, 2008 - 6:48 am UTC
read it like this then:
UPDATE x SET status = f(x.object_id)
that scalar subquery is NOT a filter, it is a function - you need a WHERE clause to filter records.
update - no where clause - updates every row of the set being updated.
In fact, this one:
UPDATE (
SELECT x.status x_status, y.status y_status
FROM x, y
WHERE x.object_id = y.object_id
)
SET x_status = y_status;
updates EVERY ROW....
in the set to be updated, which is the join of X to Y with the filter x.object_id = y.object_id
Update statement and Null-values -- update a table from another table
ravi, July 01, 2008 - 3:21 pm UTC
Hi Tom,
What is the solution for the question posted by "Anirudh" with subject : Clarification sought.
As the query returns more than one row, how the update with exists would work ?
Could you please clarify.
Long live "Tom Kyte".
July 06, 2008 - 7:22 pm UTC
the SUBquery cannot return more than one row, else it will fail.
Number of rows during merge
stephan, February 16, 2011 - 1:52 pm UTC
Hi Tom,
Having some trouble understanding the number of rows being reported as merged here... can you help me understand?
SQL> create table t2 ( resort varchar2(5), resv_name_id number, external_referen
ce_type varchar2(5), external_reference varchar2(10));
Table created.
SQL> insert into t2 values ( 'RES1', 1, 'A', '123' );
1 row created.
SQL> insert into t2 values ( 'RES1', 1, 'B', '456' );
1 row created.
SQL> insert into t2 values ( 'RES1', 2, 'B', '222' );
1 row created.
SQL> insert into t2 values ( 'RES1', 3, 'A', '223' );
1 row created.
SQL> alter table t2 add constraint t2_pk primary key (resort, resv_name_id, exte
rnal_reference_type );
Table altered.
SQL> select * from t2;
RESOR RESV_NAME_ID EXTER EXTERNAL_R
----- ------------ ----- ----------
RES1 1 A 123
RES1 1 B 456
RES1 2 B 222
RES1 3 A 223
SQL> ed
Wrote file afiedt.buf
1 merge into t2 a
2 using (select resort, resv_name_id, external_reference_type, external_refer
ence from t2 b where b.external_reference_type = 'B' ) b
3 on ( a.resort=b.resort and a.resv_name_id=b.resv_name_id )
4* when matched then update set external_reference = b.external_reference
SQL> /
3 rows merged.
SQL> select * from t2;
RESOR RESV_NAME_ID EXTER EXTERNAL_R
----- ------------ ----- ----------
RES1 1 A 456
RES1 1 B 456
RES1 2 B 222
RES1 3 A 223
So the idea here is that any resv_name_id with both 'A' and 'B' external_reference_types should have the external reference set to the 'B' value.
There's only 1 record being updated; the inner query (b) only returns 2 records.
Where does "3 rows merged" come from?
Thanks!
February 16, 2011 - 2:20 pm UTC
ops$tkyte%ORA11GR2> select *
2 from t2 a,
3 (select resort, resv_name_id, external_reference_type,
4 external_reference
5 from t2 where external_reference_type = 'B' ) b
6 where a.resort = b.resort
7 and a.resv_name_id = b.resv_name_id
8 /
RESOR RESV_NAME_ID EXTER EXTERNAL_R RESOR RESV_NAME_ID EXTER EXTERNAL_R
----- ------------ ----- ---------- ----- ------------ ----- ----------
RES1 1 A 123 RES1 1 B 456
RES1 1 B 456 RES1 1 B 456
RES1 2 B 222 RES1 2 B 222
the merge reports the number of records "merged into", not the source number of records - but the target.
Those three records are updated by your merge.
PERFECT SOLUTION
Clive Barwell, August 25, 2015 - 4:29 pm UTC
Thank you for the solution to prevent not-matching rows being set to null when updating a field with data from other tables.
The WHERE EXISTS got the job done.