See my answer. Add a comment. Active Oldest Votes. To avoid this, use NVL on the entire expression. Improve this answer. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. Email Required, but never shown. The Overflow Blog. Podcast Making Agile work for data science. Stack Gives Back Featured on Meta. New post summary designs on greatest hits now, everywhere else eventually.
I always used Exists clause for update before, I'll follow the Join Update from now. I am using 8. I want update column B incrementaly by groupping on columns A,B,C A reader, September 23, - pm UTC.
Hi Tom, I want to update one table using other table. September 23, - pm UTC. A reader, October 22, - pm UTC. How about a multi-column key on the lookup table? I have specified both columns of beta's key. January 11, - am UTC. Looks like a limitation of the key preserved code -- it wants actual "join columns" -- not a constant value.
Gary, January 20, - 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. 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.
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?
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. Your books are great! January 20, - pm UTC. MY example, with the join, ensures only that matches would be processed. I got that to work very nicely. Here is my setup: Table T: Name Null? Here is my update statement and results: update select t. 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. Great site! Great books! Many thanks from your groupies! January 28, - am UTC. Null Update? Rob, February 05, - pm UTC. You show how to insert null, what about a merge that updates nothing really a insert into where not exists. February 06, - am UTC. Rob, February 06, - am UTC. ID where TA. ID is null. February 07, - pm 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.
Tom, From a previous posting, you recommended 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. February 23, - pm 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. 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".
Thanks Tom, but what about in 8i "merge" not available , is there a way to convert this to an update of a join? February 24, - am UTC. A reader, May 27, - pm UTC. 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? Hi Tom I have table A that has a millions of rows. Table A C1, C Table A row should again become 'namea' , TableA row should become 'name99'..
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, - am UTC. Is this clear? That didnt help. I shall keep that in mind -- we are currently still on 8. I am facing problems with updating date fields. Boths fields are date fields. EMPID; commit; its working but the date is changing. The problems are like as Y2K problems. Kindly gives me a solution. I am using Oralce9i res. August 15, - 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 are apparently doing an implict "date to string to date" conversion in there somewhere. So, the example is not as you have layed out -- or one of the join dates is really "a string", not a date. Dulal, August 16, - am UTC. Tom, Thanks for the fantastic site. Could you please see the following update: update select c. 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. August 19, - pm UTC. A reader, August 20, - am UTC. But I am still on 8. 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? Appreciate the fast response.
Regards, RS. August 20, - am UTC. A reader, August 20, - pm UTC. So, is it possible for writing such updates when the lookup table is an inline view? When we can make sure programatically that the lookup table ie. 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. August 21, - am UTC. Thanks for your time, but A reader, August 22, - pm UTC. That is not what is required. 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. Any comments? August 22, - pm UTC. Elapsed: August 23, - pm UTC. I did not.
A reader, August 24, - am UTC. You have a join condition on customer. I thought you missed it, so I just added it. August 24, - am 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.
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, - pm UTC. Could you please explain the following?
August 25, - am UTC. A reader, September 19, - pm UTC. Tom, There is a table with rows. Primay key on 1 column. 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, - 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? What are the factors that need to be considered before doing an update like that? 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, - 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 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.
A little over a minute. Pls help me to resolve this prob. Thanks a lot!! November 09, - pm UTC. Thanks for your quick response. November 10, - am UTC. December 03, - pm UTC. Thanks for the ealier answer. Am I going to have a problem? December 04, - am UTC. December 07, - am UTC. Yes, But since i had third table also so just wanted to make things myself clear. December 08, - am UTC. March 07, - am UTC. Hi I was stupid, thank you. 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. It seems it cannot "go up"!? How can I move that correlation to upper level? Oracle 9. April 13, - am UTC. Which privilege am I missing?
However, removing the open cursor with the JDBC close method invalidates the positioned update. Dropping an alias invalidates a prepared update statement if the latter statement uses the alias.
Statement dependency system A searched update statement depends on the table being updated, all of its conglomerates units of storage such as heaps or indexes , all of its constraints, and any other table named in the WHERE clause or SET expressions.
0コメント