Should it really be t1. Forgot: t2. Gordon Linoff Gordon Linoff 1. Hmm, think I'm explaining it wrong, there should'nt be any doubles. I shall review me question and update it. ChesuCR 8, 4 4 gold badges 41 41 silver badges 99 99 bronze badges. Lal Lal 1. 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. I will try some test with that.
What can be solution to do this update? Thank you very much!!! February 16, - pm UTC. In the worst of cases, an application would for some reason mandate that the numbers start at one and be sequential! What if there were committed transactions over this table between filling temp table and update of the join? In other words - what if original table changes since this snapshot of rowid is taken. Can this be a problem? February 17, - pm UTC. I tried different separators, but every time I get same error.
Can this be resolved at all and how? If I remove "start with.. But for efficiency reasons it would help to find solution with "start with.. It seems that there must be wrapping inline view around inline view containing substr April 14, - am UTC. Alex, September 15, - pm UTC. Is this saying, "select the rows we are going to update September 15, - pm UTC. Peter, December 01, - am UTC. December 01, - pm UTC. More vi stuff Shannon St. Dennis, December 01, - pm UTC. I was raised using vi..
Correlated update with constant putchi, January 02, - am UTC. In a corelated update I need a unique constraint on the source table in order to get a deterministic outcome of the update. Is there any way that I can convince Oracle that the outcome is deterministic when I provide a constant value for one of the primary key columns in the source table? January 02, - am UTC. OK, but I'm currently using Oracle9i, so merge without insert is not an option at the moment.
January 02, - pm UTC. Alex, January 05, - am UTC. Hello Tom, I'm working on consolidating some code in procedures we have where they have a select procedure to select some rows, then out them to the application, then the application will call an update procedure. Obviously not efficient. The thing is though I'm terrible at writing update statements using queries so If you could just help me get it this simple example it will give me the formula for what I'm doing.
January 05, - pm UTC. I seriously doubt it. Ok I think we are on the same page. My thought was they were probably ok separate but I've seen you do a lot of cool complex updates I just didn't know if there where ways to enforce multiple conditions against the same table in an update. My update is just inefficient : it updates name and position on rows where they do not need to be. Alex, January 05, - pm UTC.
Thanks I see your point. That's what I meant by your "cool complex updates" but if it's more efficient to do two separate updates i'll stick with that. There's one other thing I'm wondering if we can do. Can we set more than one column using a select? I can find examples everywhere using one column, but not more than that. I know that doesn't work I was just wondering if there is a way to do that.
Tom, I've a table a:- empno 1 2 3 4 table b:- empno empcode 1 abcd 1 efgh 2 xxxx 2 yyyy now I add a new column to table a, called empcode and I want the empcode in table a to be updated with a value like this:- empno empcode 1 abcd,efgh 2 xxxx,yyyy. March 29, - am UTC. I'll gladly show you how to retrieve that from the two tables above which model exactly what you should be doing.
I won't show how to muck the data up and make it useless though. Could you show me how to retrieve the rows from the table? March 29, - pm UTC. I appreciate your help. August 10, - pm UTC. Tom, Thanks for the quick response. August 11, - am UTC. A reader, August 11, - am UTC. I need your help in the following cases: 1 Is this update statement wrong in the context? I believe that I am not able to share the same cursor values for all the three individual table updates.
Coudl you please help me here to correct the problem? Thanks Tom. March 02, - am UTC. Unexpectedly it's not working. Acutally, what you really mean to code is: update select Hi Tom, Please do write a followup in your free time. I expect your reply. March 04, - pm UTC. Hi Tom, Is this a good update statement? It runs for a long time though it's a correlated update.
March 22, - am UTC. I have not tried with tables with PKs though. April 24, - am UTC. The delete operates on only one table. It would normally do the key preserved table, you bypass that processing however. But the delete will only work against one of the tables and when there are more than one that it could delete from - well - you would likely want to avoid that situation and rephrase the SQL to avoid the ambiguity. Why is this correlated subquery valid?
Paul, September 05, - pm UTC. Hi Tom, Long time reader, first time poster! I'm confused why the correlated subquery doesn't give an error, in light of your earlier statement that you can only go down 1 level. This type of delete command can best be described as a delete operation on the rows of one of the table in an inline view.
I would not really recommend using this type of Delete command as it isn't clear on which table Oracle would execute the delete on. Plus, a correlated delete works just the same and is a lot clearer on which table the delete operation will be on.
The following are what I found: - At least one table has to have a unique constraint. Otherwise, you will get the error: ORA cannot delete from view without exactly one key-preserved table - If both tables have a unique constraint, the table that comes after the FROM clause, will have its record deleted. Regardless of which table comes first after the FROM clause. All Rights Reserved. Another correlated subquery Drew, April 11, - pm UTC.
There is no key to match these records on. The sub-query selects the most recent record from all the matches. I'm having trouble with the data set being a join of two "MINUS" sets - can't seem to get my head around this. April 11, - pm UTC. AND phn. Predicate for PHNX Sorry - my aliases were incorrect: AND phn. And repeated until 0 rows returned. Sounds like a 'de-dup' process or something. If you have to do this massive query over and over "until something doesn't return data anymore" - I've a feeling the only way to 'optimize' this will be to remove procedural code.
Go way back up the food chain here - toss the existing code aside for a while, re-look at the original PROBLEM and look for a more efficient method of getting from here to there. Was thinking that myself. I was given SQL to "package", and now am having to backtrack and "optimize".
But I am getting error, pl have a look at it. Class table has only rows. May 12, - pm UTC. Your example does not suffer from these errors, hence your example does not reproduce your problem, hence your example is not useful.
Tom, I was thinking Correlated subquery can only go one level down but disproved so in 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. Related Hot Network Questions. Question feed. Stack Overflow works best with JavaScript enabled. Accept all cookies Customize settings.
0コメント