Oracle sql обновить данные из другой таблицы
У меня есть база данных с account numbers и card numbers . Я сопоставляю их в файле с update любыми номерами карт и номерами счетов, так что я работаю только с номерами счетов.
Я создал представление, связывающее таблицу с базой данных учетных записей / карточек, чтобы получить соответствующий Table ID и соответствующий номер учетной записи, и теперь мне нужно обновить те записи, в которых идентификатор совпадает с номером учетной записи.
Это Sales_Import таблица, где account number поле необходимо обновить:
И это RetrieveAccountNumber таблица, откуда мне нужно обновить:
Я попробовал ниже, но пока не повезло:
Он обновляет номера карт до номеров счетов, но номера счетов заменяются на NULL
Я верю UPDATE FROM с помощью JOIN поможет:
7 Answers 7
This is called a correlated update
Assuming the join results in a key-preserved view, you could also
In your first code example: Is the outer WHERE-clause necessary for correct results? Or do you use it only to speed up the query?
@totoro - In the first example, the WHERE EXISTS prevents you from updating a row in t1 if there is no matching row in t2 . Without it, every row in t1 will be updated and the values will be set to NULL if there is no matching row in t2 . That is generally not what you want to happen so the WHERE EXISTS is generally needed.
It's worth adding that the SELECT . FROM t2 must result in a unique row. This means that you have to select on all the fields which comprise a unique key -- a non-unique primary key is not sufficient. Without uniqueness, you are reduced to something like @PaulKarr's loop -- and if there is not a unique correlation, then more than one target row may be updated for each source row.
@RachitSharma - That means that your subquery (the query from table2 ) is returning multiple rows for one or more table1 values and Oracle doesn't know which one you want to use. Normally, that means that you need to refine the subquery so that it returns a single distinct row.
I hope everybody visiting this question after 2015 notices this answer. Note that this also works if table1 and table2 are the same table, just take care of the ON -part and the WHERE -clause for the SELECT -statement of table2 !
I find that every time I need to do another merge I keep coming back to this answer for inspiration. I might print it out and frame it on my wall
t1.name = CASE WHEN t2.name is NULL THEN t1.name ELSE t2.name END -- Keeps system from Nulling out values when no value exists in t2.
The downside of this is that the SELECT statement is repeated 3 times. In complex examples that can be a deal-breaker.
never worked for me since set only expects 1 value - SQL Error: ORA-01427: single-row subquery returns more than one row.
here's the solution:
That's how exactly you run it on SQLDeveloper worksheet. They say it's slow but that's the only solution that worked for me on this case.
I didn't down rate, but it isn't a good solution. Firstly: if the subselect was returning multiple values, then the for loop will be overwriting the name on table2 multiple times for some/all records (not clean). Secondly: there is no order by clause so this will occur in an unpredictable manner (i.e. last value in unordered data wins). Thirdly: It will be much slower. Assuming the outcome of the for loop was intended, the original subselect could have been rewritten in some controlled way to return only 1 value for each record. simplest contrived way would be (select min(name). )
If you get multiple values in your subquery, you might rethink the query and use DISTINCT or GROUP BY with MIN, MAX. Just an idea.
Long story short: if you can at all avoid it, never ever EVER use any kind of LOOP in a T-SQL statement. Personally, if it wasn't for the 0.001% of the time where there's no other solution, I don't even think it should even be an available function in T-SQL. T-SQL is designed to be set-based, so it works on entire sets of data as a whole; it should NOT be used to work on data line-by-line.
Here seems to be an even better answer with 'in' clause that allows for multiple keys for the join:
The beef is in having the columns that you want to use as the key in parentheses in the where clause before 'in' and have the select statement with the same column names in parentheses. where (column1,column2) in ( select (column1,column2) from table where "the set I want" );
If your table t1 and it's backup t2 have many columns, here's a compact way to do it.
In addition, my related problem was that only some of the columns were modified and many rows had no edits to these columns, so I wanted to leave those alone - basically restore a subset of columns from a backup of the entire table. If you want to just restore all rows, skip the where clause.
Of course the simpler way would be to delete and insert as select, but in my case I needed a solution with just updates.
The trick is that when you do select * from a pair of tables with duplicate column names, the 2nd one will get named _1. So here's what I came up with:
Вопрос взят из обновления одной таблицы с данными из другой , но специально для оракула SQL.
Вам нужно вернуться к другому вопросу, отменить этот ответ и указать, что вам необходим синтаксис Oracle PLSQL.
Это называется коррелированным обновлением
Предполагая, что объединение приводит к сохранению ключа, вы также можете
В первом примере кода: необходимо ли внешнее предложение WHERE для правильных результатов? Или вы используете его только для ускорения запроса?
@totoro - В первом примере команда WHERE EXISTS предотвращает обновление строки, t1 если в ней нет соответствующей строки t2 . Без этого каждая строка t1 будет обновлена, а значения будут установлены на, NULL если в ней нет подходящей строки t2 . Как правило, это не то, что вы хотите, поэтому WHERE EXISTS обычно это необходимо.
Стоит добавить, что результатом SELECT . FROM t2 должен быть уникальный ряд. Это означает, что вы должны выбрать все поля, которые содержат уникальный ключ - неуникальный первичный ключ не является достаточным. Без уникальности вы сводитесь к чему-то вроде цикла @ PaulKarr - и если нет уникальной корреляции, то для каждой строки источника может быть обновлено более одной целевой строки.
@RachitSharma - это означает, что ваш подзапрос (запрос из table2 ) возвращает несколько строк для одного или нескольких table1 значений, и Oracle не знает, какое из них вы хотите использовать. Обычно это означает, что вам нужно уточнить подзапрос, чтобы он возвращал одну отдельную строку.
Я надеюсь, что каждый, кто посетит этот вопрос после 2015 года, заметит этот ответ. Обратите внимание, что это также работает, если table1 и table2 являются одной и той же таблицей, просто позаботьтесь о ON -part и WHERE -clause для SELECT -statement of table2 !
Я нахожу, что каждый раз, когда мне нужно сделать еще одно слияние, я продолжаю возвращаться к этому ответу для вдохновения. Я мог бы распечатать его и
Недостатком этого является то, что оператор SELECT повторяется 3 раза. В сложных примерах это может нарушить договор.
никогда не работал для меня, так как set ожидает только 1 значение - Ошибка SQL: ORA-01427: однострочный подзапрос возвращает более одной строки.
Именно так вы запускаете его на рабочем листе SQLDeveloper. Они говорят, что это медленно, но это единственное решение, которое помогло мне в этом деле.
Я не понизил рейтинг, но это не хорошее решение. Во-первых: если подвыбор возвращал несколько значений, цикл for будет перезаписывать имя в table2 несколько раз для некоторых / всех записей (не очищено). Во-вторых: не существует порядка по условию, поэтому это будет происходить непредсказуемым образом (т.е. выигрывает последнее значение в неупорядоченных данных). В-третьих: это будет намного медленнее. Предполагая, что результат цикла for был задуман, исходный подвыбор мог бы быть перезаписан каким-либо контролируемым образом, чтобы он возвращал только 1 значение для каждой записи . самый простой надуманный способ (выбрать min (имя) . )
Если вы получили несколько значений в подзапросе, вы можете переосмыслить запрос и использовать DISTINCT или GROUP BY с MIN, MAX. Просто идея.
Короче говоря: если вы вообще можете этого избежать, никогда НИКОГДА не используйте LOOP в выражении T-SQL. Лично, если бы не было 0,001% случаев, когда нет другого решения, я даже не думаю, что это должна быть даже доступная функция в T-SQL. T-SQL разработан на основе множеств, поэтому он работает на целых наборах данных в целом; это НЕ должно использоваться для работы с данными построчно.
Здесь, кажется, еще лучший ответ с предложением «in», которое позволяет использовать несколько ключей для объединения :
Суть в том, что столбцы, которые вы хотите использовать в качестве ключа в круглых скобках в предложении where перед 'in', содержат оператор select с такими же именами столбцов в круглых скобках. где ( column1, column2 ) in ( выберите ( column1, column2 ) из таблицы, где «набор, который я хочу» );
Если ваша таблица t1 и ее резервная копия t2 имеют много столбцов, вот компактный способ сделать это.
Кроме того, моя связанная с этим проблема заключалась в том, что были изменены только некоторые столбцы, и многие строки не имели правок для этих столбцов, поэтому я хотел оставить их в покое - в основном восстановить подмножество столбцов из резервной копии всей таблицы. Если вы хотите просто восстановить все строки, пропустите предложение where.
Конечно, более простым способом было бы удалить и вставить как select, но в моем случае мне нужно было решение только с обновлениями.
Хитрость в том, что когда вы выбираете * из пары таблиц с повторяющимися именами столбцов, вторая получает имя _1. Итак, вот что я придумала:
MySQL и MariaDB
Возможно, вы захотите использовать псевдоним таблицы в предложении UPDATE, иначе это вызовет проблемы, если вы в любой момент самостоятельно присоединитесь к таблице.
В предложении set вы должны изменить его SI.AccountNumber на просто, AccountNumber иначе он потерпит неудачу.
MS-Access использует другое ОБНОВЛЕНИЕ с оператором JOIN. Взгляните на: sql-und-xml.de/sql-tutorial/…
Кажется, это нормально для mssql, но не работает в mysql. Это , кажется , чтобы сделать работу , хотя: UPDATE Sales_Import, RetrieveAccountNumber SET Sales_Import.AccountNumber = RetrieveAccountNumber.AccountNumber where Sales_Import.LeadID = RetrieveAccountNumber.LeadID; . Немного не по теме, но может быть полезно
Я думаю, что нет необходимости во внутреннем соединении. Vonki решение ниже работ: UPDATE [Sales_Lead] [DBO] [Sales_Import] SET [AccountNumber] = RetrieveAccountNumber.AccountNumber FROM RetrieveAccountNumber WHERE [Sales_Lead] [DBO] [Sales_Import] .LeadID = RetrieveAccountNumber.LeadID.
Простой способ скопировать содержимое из одной таблицы в другую заключается в следующем:
Вы также можете добавить условие, чтобы скопировать определенные данные.
Это работает, но вам не нужно table2 в ОБНОВЛЕНИИ ОТ ОБНОВЛЕНИЯ table2 SET table2.col1 = table1.col1, table2.col2 = table1.col2, . ИЗ table1 WHERE table1.memberid = table2.memberid
Это не сработало, но ОБНОВЛЕНИЕ table2, table1 SET table2.col1 = table1.col1, . WHERE table1.memberid = table2.memberid (mysql и phpmyadmin)
Для SQL Server 2008 + использование, MERGE а не собственный UPDATE . FROM синтаксис имеет некоторую привлекательность.
Помимо того, что он является стандартным SQL и, следовательно, более переносимым, он также вызовет ошибку в случае наличия нескольких соединенных строк на стороне источника (и, следовательно, нескольких возможных различных значений, которые будут использоваться в обновлении), вместо того, чтобы конечный результат был недетерминированным ,
Общий ответ для будущих разработчиков.
Следует отметить, по крайней мере, для SQL Server, используйте псевдоним, а не имя таблицы в верхнем предложении обновления ( update t1. а не update Table1. )
Кажется, вы используете MSSQL, тогда, если я правильно помню, это делается так:
У меня была такая же проблема с foo.new настройкой null для строк, в foo которых не было соответствующего ключа bar . Я сделал что-то подобное в Oracle:
Потому что каждая строка в foo, не имеющая совпадения в строке, в итоге оказалась нулевой, потому что оператор select выдает null. Надеюсь, это было яснее, чем моя первая попытка объяснить это.
Для MySql, который работает нормально:
Вот что сработало для меня в SQL Server:
Спасибо за ответы. Я нашел решение, хотя.
Работает ли здесь код, вам, вероятно, стоит взглянуть на два других опубликованных решения. Они намного понятнее и гораздо менее подвержены ошибкам, а также почти наверняка быстрее.
единственное решение, которое работает для меня, потому что это стандартная инструкция обновления SQL (UPDATE SET WHERE), большое спасибо
В случае, если таблицы находятся в разных базах данных. (MSSQL)
Используйте следующий блок запроса для обновления таблицы Table1 на основе идентификатора:
Это самый простой способ решения этой проблемы.
обновить в той же таблице:
Ниже SQL кто-то предложил, не работает в SQL Server. Этот синтаксис напоминает мне мой класс старой школы:
Все остальные запросы используются NOT IN или NOT EXISTS не рекомендуются. Отображаются NULL, потому что OP сравнивает весь набор данных с меньшим подмножеством, тогда, конечно, будет проблема с сопоставлением. Это должно быть исправлено путем написания правильного SQL с правильным, JOIN а не уклонением от проблемы с помощью NOT IN . Вы можете столкнуться с другими проблемами, используя NOT IN или NOT EXISTS в этом случае.
Мой голос за верхний, который является обычным способом обновления таблицы на основе другой таблицы путем присоединения к SQL Server. Как я уже сказал, вы не можете использовать две таблицы в одном и UPDATE том же операторе в SQL Server, если сначала не присоединитесь к ним.
Я могу только сказать, что в SQL Server 2017 это работает на отлично. Так же, как записка для будущих людей. Нет необходимости присоединяться к ним.
Use the UPDATE statement to change existing values in a table or in the base table of a view or the master table of a materialized view.
For you to update values in a table, the table must be in your own schema or you must have the UPDATE object privilege on the table.
For you to update values in the base table of a view:
You must have the UPDATE object privilege on the view, and
Whoever owns the schema containing the view must have the UPDATE object privilege on the base table.
The UPDATE ANY TABLE system privilege also allows you to update values in any table or in the base table of any view.
To update values in an object on a remote database, you must also have the READ or SELECT object privilege on the object.
If the SQL92_SECURITY initialization parameter is set to TRUE and the UPDATE operation references table columns, such as the columns in a where_clause , then you must also have the SELECT object privilege on the object you want to update.
Specify a comment that passes instructions to the optimizer on choosing an execution plan for the statement.
You can place a parallel hint immediately after the UPDATE keyword to parallelize both the underlying scan and UPDATE operations.
"Hints" for the syntax and description of hints
Oracle Database Concepts for detailed information about parallel execution
The ONLY clause applies only to views. Specify ONLY syntax if the view in the UPDATE clause is a view that belongs to a hierarchy and you do not want to update rows from any of its subviews.
Specify the schema containing the object to be updated. If you omit schema , then the database assumes the object is in your own schema.
table | view | materialized_view | subquery
Specify the name of the table, view, materialized view, or the columns returned by a subquery to be updated. Issuing an UPDATE statement against a table fires any UPDATE triggers associated with the table.
If you specify view , then the database updates the base table of the view. You cannot update a view except with INSTEAD OF triggers if the defining query of the view contains one of the following constructs:
- A set operator
- A DISTINCT operator
- An aggregate or analytic function
- A GROUP BY , ORDER BY , MODEL , CONNECT BY , or START WITH clause
- A collection expression in a SELECT list
- A subquery in a SELECT list
- A subquery designated WITH READ ONLY
- A recursive WITH clause
- Joins, with some exceptions, as documented in Oracle Database Administrator's Guide
You cannot update more than one base table through a view.
In addition, if the view was created with the WITH CHECK OPTION , then you can update the view only if the resulting data satisfies the view's defining query.
If table or the base table of view contains one or more domain index columns, then this statement executes the appropriate indextype update routine.
You cannot update rows in a read-only materialized view. If you update rows in a writable materialized view, then the database updates the rows from the underlying container table. However, the updates are overwritten at the next refresh operation. If you update rows in an updatable materialized view that is part of a materialized view group, then the database also updates the corresponding rows in the master table.
Oracle Database Data Cartridge Developer's Guide for more information on the indextype update routines
CREATE MATERIALIZED VIEW for information on creating updatable materialized views
Specify the name or partition key value of the partition or subpartition within table targeted for updates. You need not specify the partition name when updating values in a partitioned table. However in some cases specifying the partition name can be more efficient than a complicated where_clause .
Specify a complete or partial name of a database link to a remote database where the object is located. You can use a database link to update a remote object only if you are using Oracle Database distributed functionality.
If you omit dblink, then the database assumes the object is on the local database.
Starting with Oracle Database 12 c Release 2 (12.2), the UPDATE statement accepts remote LOB locators as bind variables. Refer to the “Distributed LOBs” chapter in Oracle Database SecureFiles and Large Objects Developer's Guide for more information.
"References to Objects in Remote Databases" for information on referring to database links
Use the subquery_restriction_clause to restrict the subquery in one of the following ways:
Specify WITH READ ONLY to indicate that the table or view cannot be updated.
WITH CHECK OPTION
Specify WITH CHECK OPTION to indicate that Oracle Database prohibits any changes to the table or view that would produce rows that are not included in the subquery. When used in the subquery of a DML statement, you can specify this clause in a subquery in the FROM clause but not in subquery in the WHERE clause.
Specify the name of the CHECK OPTION constraint. If you omit this identifier, then Oracle automatically assigns the constraint a name of the form SYS_C n , where n is an integer that makes the constraint name unique within the database.
The table_collection_expression lets you inform Oracle that the value of collection_expression should be treated as a table for purposes of query and DML operations. The collection_expression can be a subquery, a column, a function, or a collection constructor. Regardless of its form, it must return a collection value—that is, a value whose type is nested table or varray. This process of extracting the elements of a collection is called collection unnesting .
The optional plus (+) is relevant if you are joining the TABLE collection expression with the parent table. The + creates an outer join of the two, so that the query returns rows from the outer table even if the collection expression is null.
In earlier releases of Oracle, when collection_expression was a subquery, table_collection_expression was expressed as THE subquery . That usage is now deprecated.
You can use a table_collection_expression to update rows in one table based on rows from another table. For example, you could roll up four quarterly sales tables into a yearly sales table.
Specify a correlation name (alias) for the table, view, or subquery to be referenced elsewhere in the statement. This alias is required if the DML_table_expression_clause references any object type attributes or object type methods.
Restrictions on the DML_table_expression_clause
This clause is subject to the following restrictions:
You cannot execute this statement if table or the base table of view contains any domain indexes marked IN_PROGRESS or FAILED .
You cannot insert into a partition if any affected index partitions are marked UNUSABLE .
You cannot specify the order_by_clause in the subquery of the DML_table_expression_clause .
If you specify an index, index partition, or index subpartition that has been marked UNUSABLE , then the UPDATE statement will fail unless the SKIP_UNUSABLE_INDEXES session parameter has been set to TRUE .
ALTER SESSION for information on the SKIP_UNUSABLE_INDEXES session parameter
The update_set_clause lets you set column values.
Specify the name of a column of the object that is to be updated. If you omit a column of the table from the update_set_clause , then the value of that column remains unchanged.
If column refers to a LOB object attribute, then you must first initialize it with a value of empty or null. You cannot update it with a literal. Also, if you are updating a LOB value using some method other than a direct UPDATE SQL statement, then you must first lock the row containing the LOB. See for_update_clause for more information.
If column is a virtual column, you cannot specify it here. Rather, you must update the values from which the virtual column is derived.
If column is part of the partitioning key of a partitioned table, then UPDATE will fail if you change a value in the column that would move the row to a different partition or subpartition, unless you enable row movement. Refer to the row_movement_clause of CREATE TABLE or ALTER TABLE.
In addition, if column is part of the partitioning key of a list-partitioned table, then UPDATE will fail if you specify a value for the column that does not already exist in the partition_key_value list of one of the partitions.
Specify a subquery that returns exactly one row for each row updated.
If you specify only one column in the update_set_clause , then the subquery can return only one value.
If you specify multiple columns in the update_set_clause , then the subquery must return as many values as you have specified columns.
If the subquery returns no rows, then the column is assigned a null.
If this subquery refers to remote objects, then the UPDATE operation can run in parallel as long as the reference does not loop back to an object on the local database. However, if the subquery in the DML_table_expression_clause refers to any remote objects, then the UPDATE operation will run serially without notification.
You can use the flashback_query_clause within the subquery to update table with past data. Refer to the flashback_query_clause of SELECT for more information on this clause.
Specify an expression that resolves to the new value assigned to the corresponding column.
Specify DEFAULT to set the column to the value previously specified as the default value for the column. If no default value for the corresponding column has been specified, then the database sets the column to null.
Restriction on Updating to Default Values
You cannot specify DEFAULT if you are updating a view.
The VALUE clause lets you specify the entire row of an object table.
Restriction on the VALUE clause
You can specify this clause only for an object table.
If you insert string literals into a RAW column, then during subsequent queries, Oracle Database will perform a full table scan rather than using any index that might exist on the RAW column.
The where_clause lets you restrict the rows updated to those for which the specified condition is true. If you omit this clause, then the database updates all rows in the table or view. Refer to Conditions for the syntax of condition .
The where_clause determines the rows in which values are updated. If you do not specify the where_clause , then all rows are updated. For each row that satisfies the where_clause , the columns to the left of the equality operator (=) in the update_set_clause are set to the values of the corresponding expressions to the right of the operator. The expressions are evaluated as the row is updated.
The returning clause retrieves the rows affected by a DML statement. You can specify this clause for tables and materialized views and for views with a single base table.
When operating on a single row, a DML statement with a returning_clause can retrieve column expressions using the affected row, rowid, and REFs to the affected row and store them in host variables or PL/SQL variables.
When operating on multiple rows, a DML statement with the returning_clause stores values from expressions, rowids, and REFs involving the affected rows in bind arrays.
Each item in the expr list must be a valid expression syntax.
The INTO clause indicates that the values of the changed rows are to be stored in the variable(s) specified in data_item list.
Each data_item is a host variable or PL/SQL variable that stores the retrieved expr value.
For each expression in the RETURNING list, you must specify a corresponding type-compatible PL/SQL variable or host variable in the INTO list.
The following restrictions apply to the RETURNING clause:
The expr is restricted as follows:
For UPDATE and DELETE statements each expr must be a simple expression or a single-set aggregate function expression. You cannot combine simple expressions and single-set aggregate function expressions in the same returning_clause . For INSERT statements, each expr must be a simple expression. Aggregate functions are not supported in an INSERT statement RETURNING clause.
Single-set aggregate function expressions cannot include the DISTINCT keyword.
If the expr list contains a primary key column or other NOT NULL column, then the update statement fails if the table has a BEFORE UPDATE trigger defined on it.
You cannot specify the returning_clause for a multitable insert.
You cannot use this clause with parallel DML or with remote objects.
You cannot retrieve LONG types with this clause.
You cannot specify this clause for a view on which an INSTEAD OF trigger has been defined.
Oracle Database PL/SQL Language Reference for information on using the BULK COLLECT clause to return multiple values to collection variables
The error_logging_clause has the same behavior in an UPDATE statement as it does in an INSERT statement. Refer to the INSERT statement error_logging_clause for more information.
Updating a Table: Examples
The following statement gives null commissions to all employees with the job SH_CLERK :
The following statement promotes Douglas Grant to manager of Department 20 with a $1,000 raise:
The following statement increases the salary of an employee in the employees table on the remote database:
The next example shows the following syntactic constructs of the UPDATE statement:
Both forms of the update_set_clause together in a single statement
A correlated subquery
A where_clause to limit the updated rows
The preceding UPDATE statement performs the following operations:
Updates only those employees who work in Geneva or Munich (locations 2900 and 2700)
Sets department_id for these employees to the department_id corresponding to Bombay ( location_id 2100)
Sets each employee's salary to 1.1 times the average salary of their department
Sets each employee's commission to 1.5 times the average commission of their department
Updating a Partition: Example
The following example updates values in a single partition of the sales table:
Updating an Object Table: Example
The following statement creates two object tables, people_demo1 and people_demo2 , of the people_typ object created in Table Collections: Examples. The example shows how to update a row of people_demo1 by selecting a row from people_demo2 :
The example uses the VALUE object reference function in both the SET clause and the subquery.
Correlated Update: Example
For an example that uses a correlated subquery to update nested table rows, refer to "Table Collections: Examples" .
Using the RETURNING Clause During UPDATE: Example
The following example returns values from the updated row and stores the result in PL/SQL variables bnd1 , bnd2 , bnd3 :
The following example shows that you can specify a single-set aggregate function in the expression of the returning clause:
Use the UPDATE statement to change existing values in a table or in the base table of a view or the master table of a materialized view.
For you to update values in a table, the table must be in your own schema or you must have the UPDATE object privilege on the table.
For you to update values in the base table of a view:
You must have the UPDATE object privilege on the view, and
Whoever owns the schema containing the view must have the UPDATE object privilege on the base table.
The UPDATE ANY TABLE system privilege also allows you to update values in any table or in the base table of any view.
You must also have the SELECT object privilege on the object you want to update if:
The object is on a remote database or
The SQL92_SECURITY initialization parameter is set to TRUE and the UPDATE operation references table columns, such as the columns in a where_clause .
Specify a comment that passes instructions to the optimizer on choosing an execution plan for the statement.
You can place a parallel hint immediately after the UPDATE keyword to parallelize both the underlying scan and UPDATE operations.
The ONLY clause applies only to views. Specify ONLY syntax if the view in the UPDATE clause is a view that belongs to a hierarchy and you do not want to update rows from any of its subviews.
Specify the schema containing the object to be updated. If you omit schema , then the database assumes the object is in your own schema.
table | view | materialized_view | subquery
Specify the name of the table, view, materialized view, or the columns returned by a subquery to be updated. Issuing an UPDATE statement against a table fires any UPDATE triggers associated with the table.
If you specify view , then the database updates the base table of the view. You cannot update a view except with INSTEAD OF triggers if the defining query of the view contains one of the following constructs:
You cannot update more than one base table through a view.
In addition, if the view was created with the WITH CHECK OPTION , then you can update the view only if the resulting data satisfies the view's defining query.
If table or the base table of view contains one or more domain index columns, then this statement executes the appropriate indextype update routine.
You cannot update rows in a read-only materialized view. If you update rows in a writable materialized view, then the database updates the rows from the underlying container table. However, the updates are overwritten at the next refresh operation. If you update rows in an updatable materialized view that is part of a materialized view group, then the database also updates the corresponding rows in the master table.
Oracle Data Cartridge Developer's Guide for more information on the indextype update routines
CREATE MATERIALIZED VIEW for information on creating updatable materialized views
Specify the name of the partition or subpartition within table targeted for updates. You need not specify the partition name when updating values in a partitioned table. However in some cases specifying the partition name can be more efficient than a complicated where_clause .
Specify a complete or partial name of a database link to a remote database where the object is located. You can use a database link to update a remote object only if you are using Oracle Database distributed functionality.
If you omit dblink, then the database assumes the object is on the local database.
"Referring to Objects in Remote Databases" for information on referring to database links
Use the subquery_restriction_clause to restrict the subquery in one of the following ways:
WITH READ ONLY Specify WITH READ ONLY to indicate that the table or view cannot be updated.
WITH CHECK OPTION Specify WITH CHECK OPTION to indicate that Oracle Database prohibits any changes to the table or view that would produce rows that are not included in the subquery. When used in the subquery of a DML statement, you can specify this clause in a subquery in the FROM clause but not in subquery in the WHERE clause.
CONSTRAINT constraint Specify the name of the CHECK OPTION constraint. If you omit this identifier, then Oracle automatically assigns the constraint a name of the form SYS_C n , where n is an integer that makes the constraint name unique within the database.
The table_collection_expression lets you inform Oracle that the value of collection_expression should be treated as a table for purposes of query and DML operations. The collection_expression can be a subquery, a column, a function, or a collection constructor. Regardless of its form, it must return a collection value—that is, a value whose type is nested table or varray. This process of extracting the elements of a collection is called collection unnesting .
The optional plus (+) is relevant if you are joining the TABLE expression with the parent table. The + creates an outer join of the two, so that the query returns rows from the outer table even if the collection expression is null.
In earlier releases of Oracle, when collection_expression was a subquery, table_collection_expression was expressed as THE subquery . That usage is now deprecated.
You can use a table_collection_expression to update rows in one table based on rows from another table. For example, you could roll up four quarterly sales tables into a yearly sales table.
Specify a correlation name (alias) for the table, view, or subquery to be referenced elsewhere in the statement. This alias is required if the DML_table_expression_clause references any object type attributes or object type methods.
Restrictions on the DML_table_expression_clause This clause is subject to the following restrictions:
You cannot execute this statement if table or the base table of view contains any domain indexes marked IN_PROGRESS or FAILED .
You cannot insert into a partition if any affected index partitions are marked UNUSABLE .
You cannot specify the order_by_clause in the subquery of the DML_table_expression_clause .
If you specify an index, index partition, or index subpartition that has been marked UNUSABLE , then the UPDATE statement will fail unless the SKIP_UNUSABLE_INDEXES session parameter has been set to TRUE .
ALTER SESSION for information on the SKIP_UNUSABLE_INDEXES session parameter
The update_set_clause lets you set column values.
Specify the name of a column of the object that is to be updated. If you omit a column of the table from the update_set_clause , then the value of that column remains unchanged.
If column refers to a LOB object attribute, then you must first initialize it with a value of empty or null. You cannot update it with a literal. Also, if you are updating a LOB value using some method other than a direct UPDATE SQL statement, then you must first lock the row containing the LOB. See for_update_clause for more information.
If column is part of the partitioning key of a partitioned table, then UPDATE will fail if you change a value in the column that would move the row to a different partition or subpartition, unless you enable row movement. Please refer to the row_movement_clause of CREATE TABLE or ALTER TABLE.
In addition, if column is part of the partitioning key of a list-partitioned table, then UPDATE will fail if you specify a value for the column that does not already exist in the partition_value list of one of the partitions.
Specify a subquery that returns exactly one row for each row updated.
If you specify only one column in the update_set_clause , then the subquery can return only one value.
If you specify multiple columns in the update_set_clause , then the subquery must return as many values as you have specified columns.
If the subquery returns no rows, then the column is assigned a null.
If this subquery refers to remote objects, then the UPDATE operation can run in parallel as long as the reference does not loop back to an object on the local database. However, if the subquery in the DML_table_expression_clause refers to any remote objects, then the UPDATE operation will run serially without notification.
You can use the flashback_query_clause within the subquery to update table with past data. Please refer to the flashback_query_clause of SELECT for more information on this clause.
Specify an expression that resolves to the new value assigned to the corresponding column.
DEFAULT Specify DEFAULT to set the column to the value previously specified as the default value for the column. If no default value for the corresponding column has been specified, then the database sets the column to null.
Restriction on Updating to Default Values You cannot specify DEFAULT if you are updating a view.
The VALUE clause lets you specify the entire row of an object table.
Restriction on the VALUE clause You can specify this clause only for an object table.
If you insert string literals into a RAW column, then during subsequent queries, Oracle Database will perform a full table scan rather than using any index that might exist on the RAW column.
The where_clause lets you restrict the rows updated to those for which the specified condition is true. If you omit this clause, then the database updates all rows in the table or view. Please refer to Chapter 7, "Conditions" for the syntax of condition .
The where_clause determines the rows in which values are updated. If you do not specify the where_clause , then all rows are updated. For each row that satisfies the where_clause , the columns to the left of the equality operator (=) in the update_set_clause are set to the values of the corresponding expressions to the right of the operator. The expressions are evaluated as the row is updated.
The returning clause retrieves the rows affected by a DML statement. You can specify this clause for tables and materialized views and for views with a single base table.
When operating on a single row, a DML statement with a returning_clause can retrieve column expressions using the affected row, rowid, and REFs to the affected row and store them in host variables or PL/SQL variables.
When operating on multiple rows, a DML statement with the returning_clause stores values from expressions, rowids, and REFs involving the affected rows in bind arrays.
expr Each item in the expr list must be a valid expression syntax.
INTO The INTO clause indicates that the values of the changed rows are to be stored in the variable(s) specified in data_item list.
data_item Each data_item is a host variable or PL/SQL variable that stores the retrieved expr value.
For each expression in the RETURNING list, you must specify a corresponding type-compatible PL/SQL variable or host variable in the INTO list.
Restrictions The following restrictions apply to the RETURNING clause:
The expr is restricted as follows:
For UPDATE and DELETE statements each expr must be a simple expression or a single-set aggregate function expression. You cannot combine simple expressions and single-set aggregate function expressions in the same returning_clause . For INSERT statements, each expr must be a simple expression. Aggregate functions are not supported in an INSERT statement RETURNING clause.
Single-set aggregate function expressions cannot include the DISTINCT keyword.
If the expr list contains a primary key column or other NOT NULL column, then the update statement fails if the table has a BEFORE UPDATE trigger defined on it.
You cannot specify the returning_clause for a multitable insert.
You cannot use this clause with parallel DML or with remote objects.
You cannot retrieve LONG types with this clause.
You cannot specify this clause for a view on which an INSTEAD OF trigger has been defined.
PL/SQL User's Guide and Reference for information on using the BULK COLLECT clause to return multiple values to collection variables
The error_logging_clause has the same behavior in an UPDATE statement as it does in an INSERT statement. Please refer to the INSERT statement error_logging_clause for more information.
Updating a Table: Examples The following statement gives null commissions to all employees with the job SH_CLERK :
The following statement promotes Douglas Grant to manager of Department 20 with a $1,000 raise:
The following statement increases the salary of an employee in the employees table on the remote database:
The next example shows the following syntactic constructs of the UPDATE statement:
Both forms of the update_set_clause together in a single statement
A correlated subquery
A where_clause to limit the updated rows
The preceding UPDATE statement performs the following operations:
Updates only those employees who work in Geneva or Munich (locations 2900 and 2700)
Sets department_id for these employees to the department_id corresponding to Bombay ( location_id 2100)
Sets each employee's salary to 1.1 times the average salary of their department
Sets each employee's commission to 1.5 times the average commission of their department
Updating a Partition: Example The following example updates values in a single partition of the sales table:
Updating an Object Table: Example The following statement creates two object tables, people_demo1 and people_demo2 , of the people_typ object created in Table Collections: Examples. The example shows how to update a row of people_demo1 by selecting a row from people_demo2 :
The example uses the VALUE object reference function in both the SET clause and the subquery.
Correlated Update: Example For an example that uses a correlated subquery to update nested table rows, please refer to "Table Collections: Examples".
Using the RETURNING Clause During UPDATE: Example The following example returns values from the updated row and stores the result in PL/SQL variables bnd1 , bnd2 , bnd3 :
The following example shows that you can specify a single-set aggregate function in the expression of the returning clause:
Question is taken from update one table with data from another, but specifically for oracle SQL.
You need to go back to your other question, un-accept that answer, and state specifically that you need the Oracle PLSQL syntax.
MS SQL
Читайте также: