Московский государственный университет имени М.В.Ломоносова
Опубликован: 10.10.2005 | Доступ: свободный | Студентов: 8109 / 488 | Оценка: 3.85 / 3.50 | Длительность: 22:03:00
Лекция 7:

Средства манипулирования данными

Оператор UPDATE для модификации существующих строк в существующих таблицах

Общий синтаксис оператора UPDATE выглядит следующим образом:

UPDATE table_name SET update_assignment_commalist
    WHERE conditional_expression
update_assignment ::= column_name =
    { value_expression | DEFAULT | NULL }

Семантика оператора модификации существующих строк определяется следующим образом:

  1. для всех строк таблицы с именем table_name вычисляется булевское выражение conditional_expression. Строки, для которых значением этого булевского выражения является true, считаются подлежащими модификации (обозначим множество таких строк через Tm );
  2. каждая строка s ( s Tm ) подвергается модификации таким образом, что значение каждого столбца этой строки, указанного в списке update_assignment_commalist, заменяется значением, указанным в правой части соответствующего элемента списка модификации2Если в правой части элемента модификации присутствует value_expression, в котором содержится запрос, то в случае использования в этом запросе имен столбцов модифицируемой таблицы под значениями этих столбцов понимается значение до модификации.. Значения столбцов строки s, не указанные в списке модификации, остаются неизменными.

Приведем примеры операций модификации таблиц.

UPDATE EMP SET DEPT_NO = 632, EMP_SAL = EMP_SAL + 1000.00
   WHERE PRO_NO = 772;
Пример 17.5. Перевести всех служащих, выполняющих проект с номером 772, в отдел 632 и повысить им заработную плату на 1000 руб.

При выполнении данной операции на первом шаге в таблице EMP будут найдены все строки, относящиеся к служащим, которые участвуют в проекте с номером 772. На втором шаге во всех этих строках значение столбца DEPT_NO будет изменено на 632, а к значению столбца EMP_SAL будет прибавлено 1000.00.

UPDATE EMP SET EMP_SAL = (SELECT AVG (EMP1_SAL)
     FROM EMP EMP1
     WHERE EMP.DEPT_NO = EMP1.DEPT_NO)
         + 1000.00, PRO_NO = NULL
     WHERE (SELECT EMP1.EMP_SAL
          FROM EMP EMP1, DEPT
          WHERE EMP.DEPT_NO = DEPT.DEPT_NO
             AND DEPT_MNG = EMP1.EMP_NO) > 30000.00;
Пример 17.6. Для всех служащих, работающих в отделах, заработная плата менеджеров которых превышает 30000 руб., установить размер заработной платы, на 1000 руб. превышающий средний размер заработной платы соответствующего отдела, а номера проектов, в которых участвуют эти служащие, сделать неопределенными.

Конечно, если вам больше нравится другой стиль, то запрос, фигурирующий в разделе WHERE, можно переформулировать с использованием вложенного подзапроса (пример 17.6a).

UPDATE EMP SET EMP_SAL = (SELECT AVG (EMP1_SAL)
     FROM EMP EMP1
     WHERE EMP.DEPT_NO = EMP1.DEPT_NO)
         + 1000.00, PRO_NO = NULL
     WHERE DEPT.NO IN (SELECT DEPT.DEPT_NO
          FROM EMP, DEPT
          WHERE DEPT_MNG = EMP_NO
          AND EMP_SAL > 30000.00);
Пример 17.6a.

Эти примеры позволяют понять, насколько богаты возможности оператора UPDATE . В разделе WHERE может содержаться любое условие, допускаемое в операторе выборки, а в элементах списка раздела SET может присутствовать любой вид value_expression, в том числе любой запрос, вырабатывающий одиночное значение (скалярный подзапрос).

Оператор DELETE для удаления строк в существующих таблицах

Общий синтаксис оператора DELETE выглядит следующим образом:

DELETE FROM table_name
   WHERE conditional_expression

В некотором смысле оператор DELETE является частным случаем оператора UPDATE (или, наоборот, действие оператора UPDATE представляет собой комбинацию действий операторов DELETE и INSERT ).

Семантика оператора модификации существующих строк определяется следующим образом:

  1. для всех строк таблицы с именем table_name вычисляется булевское выражение conditional_expression. Строки, для которых значением этого булевского выражения является true, считаются подлежащими удалению (обозначим множество таких строк через Td );
  2. каждая строка s ( s Td ) удаляется из указанной таблицы.

С целью иллюстрации приведем два примера операции удаления строк.

DELETE FROM EMP WHERE PRO_NO = 772;
Пример 17.7. Удалить из таблицы EMP все строки, относящиеся к служащим, которые участвуют в проекте с номером 772.
DELETE FROM EMP WHERE EMP_SAL >
     (SELECT EMP1.EMP_SAL
          FROM EMP EMP1, DEPT
          WHERE EMP.DEPT_NO = DEPT.DEPT_NO
             AND DEPT.DEPT.MNG = EMP1.EMP_NO);
Пример 17.8. Удалить из таблицы EMP все строки, относящиеся к служащим, размер заработной платы которых превышает размер заработной платы менеджеров их отделов.

Как и в операторе UPDATE , в разделе WHERE оператора DELETE можно использовать любой вид булевского выражения, допустимого в операторе выборки. Поэтому возможности оператора удаления строк ограничены лишь фантазией пользователя.

Алексей Ковтун
Алексей Ковтун

При попытке исполнения запроса:

CREATE DOMAIN EMP_NO AS INTEGER

    CHECK (VALUE BETWEEN 1 AND 10000);

Выдается ошибка: Неизвестный тип объекта "DOMAIN" в интсрукции CREATE, DROP или ALTER. 

Используется SQL Server MS SQL 2008R2

Александра Каева
Александра Каева
Сергей Пантелеев
Сергей Пантелеев
Россия, Москва
Геннадий Шестаков
Геннадий Шестаков
Беларусь, Орша