Improve this question. Alexis Dufrenoy Sorry I just abbreviated the original column name to desc its obviously not that in the db — user Add a comment. Active Oldest Votes. That syntax isn't valid in Oracle. NEW It depends if the inline view is considered updateable by Oracle To be updatable for the second statement depends on some rules listed here.
Improve this answer. Tony Andrews Tony Andrews k 20 20 gold badges silver badges bronze badges. I did the second example but had to add aliases to the column names in the select and then reference them by their names in the SET but it worked, thanks — Gustavo Rubio. The second example has the benefit of allowing you to test the SQL before actually performing the update.
The second example worked for me. I like that one because it looks clean and readable. Don't know what the pros and cons are between the two when it comes to performance. But, I wasn't worried about that for now 'cuz I used this for a one off script to correct bad data. Explanation on key-preserved requirement for updatable joins: asktom. Got this - "ORA cannot modify a column which maps to a non key-preserved table", and then stackoverflow.
Show 7 more comments. Quassnoi Quassnoi k 88 88 gold badges silver badges bronze badges. Works perfectly but Oracle required me to say merge into table 1 t and so forth. Late to the party, but this is still a good thread. I need to know, tho' Master table, "table1". Table2, aliased as t2, but in the ON, the references are External Table1 - not t1 - is this a reference to the outer table or a type?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up. Connect and share knowledge within a single location that is structured and easy to search.
I want to update my db records, which are related one to many. Please consider the following example :. Table person has phone and fax columns. Now I added phone and fax columns to the contact table and I want to copy the values of fax and column to the contact table from person table. The error points to a syntax problem. When you want to update multiple columns at once, you list all of the columns to be updated first, followed by all of the values to use in the same order that the columns are listed.
For example:. Mainly, there are a couple of syntaxes to perform a query update in Oracle either by depending on a traditional update or modifying one table with data records from another table.
Firstly, let us view the syntax for Oracle update with Join clause to update a single table typed as follows:. The other type of oracle syntax for update with join while updating a single table with data from the other table is as follows:.
The Update with Join query works in Oracle by the following steps where we have supposed to have two tables Orders and OrderInfo like shown below which will be simple to elaborate also:.
Now, we will perform the update query with JOIN clause on table orderInfo based on the Orders table column values so that the resultant table provides an output of join using the query command as:. OrderNum SET orderinfo. In the same way, we can use the left join clause with the update statement so that the query results or updates the table columns on the left side of the LEFT JOIN clause according to the matched values otherwise with NULL.
To update single table column: We will execute a simple update query statement in Oracle written below and also view the table named Orders first to see the column values present as follows:. I've put this result as the table to update. It is a workaround, but it works! Only thing you have to be careful about is ensuring that the results are unique and you are not trying to update more rows than the ones you need.
This is why I always do a select before to check how many rows should be updated. This should be written like this:. As you can see, it is more readable than the previous solution. Keep this in mind when using it and test it before use in a production environment. What about PostgreSQL? We have just a few differences with the syntax as we do not specify the join. In this case we do not need to specify the first table on which we will do the update.
The rest is exactly the same as in SQL Server. Let's test the code with the CTE please note that in PostgreSQL we need to put all column names that have been created with a capital letter under quotes otherwise it will not recognize them!
0コメント