Решение

Для поиска и возвращения новых значений COMM в выражение UPDATE используйте объединение таблиц NEW SAL и EMP. Довольно часто такие обновления реализуются посредством связанных подзапросов. Другая техника предполагает создание представления (обычного или вложенного, в зависимости от того, что поддерживает СУБД) и последующее его обновление.

DB2 и MySQL

С помощью связанного подзапроса задайте новые значения столбцам SAL и COMM в таблице EMP. Используйте также связаный подзапрос, чтобы определить, какие строки EMP подлежат обновлению:

1 update emp e set (e.sal,e.comm) = (select ns.sal, ns.sal/2

2 from new sal ns

3 where ns.deptno=e.deptno)

4 where exists ( select null

5 from new sal ns

6 where ns.deptno = e.deptno )

Oracle

Метод, используемый в решении для DB2, несомненно, подходит и для Oracle, но в качестве альтернативы можно использовать в выражении UPDATE вложенное представление:

1 update (

2 select e.sal as emp sal, e.comm as emp comm,

3 ns.sal as ns sal, ns.sal/2 as ns comm

4 from emp e, new sal ns

5 where e.deptno = ns.deptno

6 ) set emp sal = ns sal, emp comm = ns comm

PostgreSQL

Метод, используемый в решении для DB2, подойдет и для PostgreSQL, но в качестве альтернативы можно (что довольно удобно) проводить объединение непосредственно в выражении UPDATE:

1 update emp

2 set sal = ns.sal,

3 comm = ns.sal/2

4 from new sal ns

5 where ns.deptno = emp.deptno

SQL Server

Необходимо (как и в решении для PostgreSQL) проводить объединение прямо в выражении UPDATE:

1 update e

2 set e.sal = ns.sal,

3 e.comm = ns.sal/2

4 from emp e,

5 new sal ns

6 where ns.deptno = e.deptno

Обсуждение

Прежде чем приступить к обсуждению решений, мне хотелось бы напомнить несколько важных моментов относительно обновлений, при которых запросы используются для получения новых значений. Блок WHERE в подзапросе связанного обновления и блок WHERE, применяемый к обновляемой таблице, - совершенно разные вещи. Посмотрим на выражение UPDATE, представленное в разделе Решение : происходит объединение таблиц EMP и NEW SAL по столбцу DEPT-NO, и строки возвращаются в оператор SET выражения UPDATE. Для служащих 10-го отдела (DEPTNO 10) возвращаются значения, потому что в таблице NEW SAL есть соответствующие им значения DEPTNO. А что же со служащими других отделов? В NEW SAL иные отделы не представлены, поэтому столбцам SAL и COMM для служащих других отделов (DEPTNO 20 и 30) присваиваются значения NULL. Если производитель не предоставляет какого-либо механизма ограничения числа строк, возвращаемых в результирующем множестве (например, функции LIMIT или TOP), то единственным средством для этого в SQL является использование блока WHERE. Правильное выполнение этого UPDATE обеспечивает блок WHERE, применяемый к обновляемой таблице, в сочетании с блоком WHERE в связанном запросе.

DB2 и MySQL

Чтобы в таблице EMP обновлялись не все строки, необходимо включить в блок WHERE выражения UPDATE связанный подзапрос. Выноса объединения (связанный подзапрос) в оператор SET недостаточно. Использование блока WHERE в UPDATE гарантирует, что обновлены будут только те строки таблицы EMP, значение столбца DEPTNO которых совпадает со значением этого столбца в таблице NEW SAL. Это правомочно для всех СУБД.

Oracle

В решении Oracle с обновлением объединенного представления для выявления строк, подлежащих обновлению, используются эквиобъ-единения. Убедиться в том, какие строки обновляются, можно, выполнив запрос отдельно. Чтобы успешно пользоваться обновлением такого типа, необходимо прежде всего понять принцип сохранения ключей. Столбец DEPTNO таблицы NEW SAL является первичным ключом этой таблицы, таким образом, его значения уникальны в рамках таблицы. Однако при объединении таблиц EMP и NEW SAL значения столбца NEW SAL.DEPTNO в результирующем множестве не уникальны, как можно видеть ниже:

select e.empno, e.deptno e dept, ns.sal, ns.deptno ns deptno from emp e, new sal ns where e.deptno = ns.deptno

Чтобы в Oracle можно было провести обновление этого объединения, одна из таблиц должна быть сохраняющей ключи (key-preserved). Это означает, что если значения неуникальны в результирующем множестве, то они должны быть уникальными, по крайней мере, в своей исходной таблице. В данном случае DEPTNO является первичным ключом NEW SAL, что делает его значения уникальными в этой таблице. Они могут многократно повторяться в результирующем множестве, но исходная таблица все равно остается сохраняющей ключи, поскольку в ней эти значения уникальны, что позволяет успешно выполнить обновление.

PostgreSQL и SQL Server

Синтаксис для этих двух платформ немного отличается, но принцип тот же. Очень удобно осуществлять объединение непосредственно в выражении UPDATE. Поскольку обновляемая таблица задается явно (таблицы перечисляются после ключевого слова UPDATE), то не возникает путаницы с тем, строки какой таблицы изменяются. Кроме того, в связи с тем, что объединения осуществляются в процессе обновления (поскольку присутствует явный блок WHERE), можно избежать некоторых ошибок при реализации обновлений с использованием связанных подзапросов; в частности, если здесь пропустить объединение, то ошибка будет очевидной.

Слияние записей Задача

Требуется вставлять, обновлять или удалять записи таблицы на основании условия существования или отсутствия соответствующих записей. (Если запись существует, проводится ее обновление; если нет, то она вставляется; если после обновления строка не отвечает определенному условию, то она удаляется.) Например, необходимо изменить таблицу EMP COMMISSION по следующим правилам:

Если запись о служащем, представленном в EMP COMMISSION, также существует в таблице EMP, обновить его комиссионные

(COMM) до 1000.

Удалить всех служащих, которым потенциально обеспечено повышение комиссионных (COMM) до 1000, если их заработная плата

(SAL) меньше 2000 (их не должно быть в EMP COMMISSION).