Oracle Database
--
Questions
--
Followers
Top Experts
Oracle Update using Inner Join
Hi All,
I am trying to update table1.field3 with the value from table2.field3, based on joining these
two tables that have a common unique value. The problem that I am trying to get past,
is being able to set the table1.field3 value to the table2.field3, which is contained in the
inner join query. Also, the field name is the same in both tables.
This is the current SQL Query that I have created thus far,but upon executing the SQL Query I am
getting the error message of:
"ORA-00933 SQL command not properly ended"
SQL Query
Update table1
set table1.field3 = table2.field3 (the field name is the same in both tables)
FROM table1 a
inner join table2 b
on a.unqiue_value = b.unqiue_value
I really appreciate any assistance that can be provided.
Thanks!
I am trying to update table1.field3 with the value from table2.field3, based on joining these
two tables that have a common unique value. The problem that I am trying to get past,
is being able to set the table1.field3 value to the table2.field3, which is contained in the
inner join query. Also, the field name is the same in both tables.
This is the current SQL Query that I have created thus far,but upon executing the SQL Query I am
getting the error message of:
"ORA-00933 SQL command not properly ended"
SQL Query
Update table1
set table1.field3 = table2.field3 (the field name is the same in both tables)
FROM table1 a
inner join table2 b
on a.unqiue_value = b.unqiue_value
I really appreciate any assistance that can be provided.
Thanks!
Zero AI Policy
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
SOLUTION
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
Hi Mwvisa1,
After doing some more research on the Oracle development network website, I was able to create this SQL query. And, it seems to work well. The only thing that I don't like is that it takes 3+ minutes
to update 3,000 rows.
update table1a
set table1.field3 = (select table2.field3
from table2 b
where a.field3 = b.field3
where a.uniqueid in (select unqiueid from table2)
Also, I tried your SQL query and received the following error message:
ORA-01779: cannot modify a column which maps to a non key-preserved table.
Is this correct?
After doing some more research on the Oracle development network website, I was able to create this SQL query. And, it seems to work well. The only thing that I don't like is that it takes 3+ minutes
to update 3,000 rows.
update table1a
set table1.field3 = (select table2.field3
from table2 b
where a.field3 = b.field3
where a.uniqueid in (select unqiueid from table2)
Also, I tried your SQL query and received the following error message:
ORA-01779: cannot modify a column which maps to a non key-preserved table.
Is this correct?
How big table1, table2 tables are?
Please provide us All Indexes in all tables along with columns.
Is there any null value return from inner query
"where a.uniqueid in (select unqiueid from table2)"
because null will let table1 to eliminate Index Scan.
Please provide us All Indexes in all tables along with columns.
Is there any null value return from inner query
"where a.uniqueid in (select unqiueid from table2)"
because null will let table1 to eliminate Index Scan.
Your query should be like this....
update table1 a
set a.field3 = (select b.field3
from table2 b
where a.unqiue_value = b.unqiue_value)
Index on a.unqiue_value and b.unqiue_value help you to improve perf. (only if you found it BAD !!)
update table1 a
set a.field3 = (select b.field3
from table2 b
where a.unqiue_value = b.unqiue_value)
Index on a.unqiue_value and b.unqiue_value help you to improve perf. (only if you found it BAD !!)
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
virdi ds,
I apologize for the delayed response. But, tables 1 & 2 contain the same number of 2600+ rows.
And, there are no null uniqueid values that exist in either of the two tables. Thus, the uniqueid values for the 2600 rows in both tables are 100% unique. And, there isn't an index that exists for the uniqueid field, in the tables 1 or 2. As stated by POracle thsi would probably help to improve the query's performance.
Thanks.
I apologize for the delayed response. But, tables 1 & 2 contain the same number of 2600+ rows.
And, there are no null uniqueid values that exist in either of the two tables. Thus, the uniqueid values for the 2600 rows in both tables are 100% unique. And, there isn't an index that exists for the uniqueid field, in the tables 1 or 2. As stated by POracle thsi would probably help to improve the query's performance.
Thanks.
POracle,
Thanks for pointing that out. But, that was just a typo on my part. The actual SQL Query is already joining where a.unqiue_value = b.unqiue_value, as you described. Thanks!
Thanks for pointing that out. But, that was just a typo on my part. The actual SQL Query is already joining where a.unqiue_value = b.unqiue_value, as you described. Thanks!
Where clause also not needed..
where a.uniqueid in (select unqiueid from table2)
If your query is correct as I indicate than go for indexing...
where a.uniqueid in (select unqiueid from table2)
If your query is correct as I indicate than go for indexing...
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
POracle,
I appreciate the reply. I didn't mention that the values in field3 aren't unique. So, that query wouldn't work. And, upon query execution would lead to the following error message being generated:
Error report:
SQL Error: ORA-01427: single-row subquery returns more than one row
01427. 00000 - "single-row subquery returns more than one row"
So, the second subquery helps to resolve this problem. Any thoughts?
Thanks again.
I appreciate the reply. I didn't mention that the values in field3 aren't unique. So, that query wouldn't work. And, upon query execution would lead to the following error message being generated:
Error report:
SQL Error: ORA-01427: single-row subquery returns more than one row
01427. 00000 - "single-row subquery returns more than one row"
So, the second subquery helps to resolve this problem. Any thoughts?
Thanks again.
ASKER CERTIFIED SOLUTION
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
POracle,
You are absolutely correct! I executed my query in a simulated environment other than the real environment, where there aren't any duplicate b.unique values. And your query works.
Adding the index on tables 1 and 2, reduced the time to 23secs!
Thanks again!
You are absolutely correct! I executed my query in a simulated environment other than the real environment, where there aren't any duplicate b.unique values. And your query works.
Adding the index on tables 1 and 2, reduced the time to 23secs!
Thanks again!
I really appreciate your help POracle and MWvisa1.
\
\
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
You are most welcome.
Sorry for delays in response, but glad that POracle was around to help in my stead.
Best regards and happy coding,
Kevin
Sorry for delays in response, but glad that POracle was around to help in my stead.
Best regards and happy coding,
Kevin
Oracle Database
--
Questions
--
Followers
Top Experts
Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.