14. Удаление данных (инструкция delete)

Инструкция DELETE удаляет выбранные строки из одной таблицы.

delete FROM имя_таблицы

[WHERE условие];

В предложении FROM указывается таблица или представление, содержащие строки, которые требуется удалить. В предложении WHERE указывается критерий отбора строк, которые должны быть удалены.

Предположим, что недавно принятый на работу Иванов решил уволиться из компании. Инструкция DELETE, удаляющая относящуюся к служащему строку из таблицы STAFF выглядит следующим образом:

delete FROM staff

WHERE lname=‘Иванов’;

Условия отбора, которые можно задать в предложении WHERE инструкции DELETE, полностью совпадают с условиями отбора, доступными в одноименном предложении инструкции SELECT рассмотренными ранее.

Удаление всех строк. Хотя предложение WHERE в инструкции DELETE является необязательным, оно присутствует почти всегда. Если же оно отсутствует, то удаляются все таблицы. Например:

Удалить информацию обо всех сотрудниках организации:

DELETE FROM staff;

В результате выполнения приведенной инструкции таблица STAFF становится пустой, но из базы данных она не удаляется, так как определение таблицы и ее столбцов остается и в нее по-прежнему можно добавлять новые строки. Чтобы удалить из базы данных определение таблицы, необходимо использовать DDL-инструкцию DROP TABLE.

Инструкция DELETE, приведенная в примере несет в себе потенциальную угрозу удаления необходимых строк, поэтому всегда следует задавать условие отбора и обращать внимание на то, отбирает ли оно действительно ненужные строки. Желательно вначале проверить предложение WHERE в интерактивном режиме в составе инструкции SELECT и отобразить выбранные строки на экране. Убедившись, что это именно те строки, которые требуется удалить, можно использовать предложение WHERE в инструкции на удаление.

Инструкция DELETE с подчиненным запросом. Инструкции DELETE с простыми условиями отбора отбирают строки для удаления исключительно на основании содержимого этих строк. Но иногда отбор строк необходимо производить, опираясь на данные из других таблиц. Предположим, вы хотите удалить все объекты, принадлежащие какому-либо владельцу. Не зная его идентификатора, невозможно найти принадлежащие ему объекты. Чтобы найти эти объекты, надо обратиться с запросом к таблице владельцев (OWNER). Для решения подобных задач SQL предоставляет возможность задания подзапросов в предложении WHERE инструкции на удаление. Задача удаления информации об объектах, принадлежащих владельцу Иванову, решается следующим образом:

DELETE FROM property_for_rent

WHERE property_for_rent.ono=(SELECT ono

FROM owner

WHERE lname=‘Иванов’);

Подчиненные запросы в предложении WHERE могут иметь несколько уровней вложенности. Они могут также содержать внешние ссылки на целевую таблицу инструкции DELETE. В этом случае предложение FROM инструкции DELETE играет такую же роль, как и предложение FROM инструкции SELECT.

15. Обновление данных (инструкция update)

Инструкция UPDATE обновляет значения одного или нескольких столбцов в выбранных строках одной таблицы.

update имя_таблицы

set имя_столбца_1=значение_1 [, имя_столбца_2=значение_2 …]

[WHERE условие];

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

Увеличить заработную плату менеджеров на 10%

update staff

set salary=salary*1.1

WHERE position=‘manager’;

Условия отбора, которые могут быть заданы в предложении WHERE инструкции UPDATE, в точности соответствуют условиям отбора, доступным в инструкциях SELECT и DELETE.

Как и инструкция DELETE, инструкция UPDATE может одновременно обновить несколько строк, соответствующих условию отбора. Например:

Перевести всех служащих из отделения № 2 в отделение № 1

update staff

set bno=1

WHERE bno=2;

Предложение SET в инструкции представляет собой список операций присваивания, отделяемых друг от друга запятыми. В каждой операции идентифицируется столбец, который должен обновляться, и определяется новое значение для этого столбца. Каждый целевой столбец должен встречаться в списке только один раз. Согласно стандарту ANSI/ISO, для столбцов необходимо использовать простые имена, но некоторые СУБД допускают использование полных имен столбцов.

Присваиваемое значение может быть сформировано с помощью выражения. Выражение в операции присваивания может быть любым допустимым SQL-выражением, результирующее значение которого имеет тип данных, соответствующий целевому столбцу. Необходимо, чтобы значение выражения вычислялось на основе значений строки, которая в данный момент обновляется в целевой таблице. Оно не может включать в себя какие-либо статистические функции или подчиненные запросы.

Если выражение в операции присваивания содержит ссылку на один из столбцов таблицы, то для вычисления выражения используется значение этого столбца в текущей строке, которое было перед обновлением. То же самое справедливо для ссылок на столбцы в предложении WHERE.

Обновление всех строк. Предложение WHERE в инструкции UPDATE является необязательным. Если оно опущено, то обновляются все строки целевой таблицы. Например:

Увеличить заработную плату всех сотрудников на 5%:

update staff

set salary=1.05*salary;

Инструкции UPDATE с подчиненным запросом. В инструкции UPDATE, так же как и в инструкции DELETE, подчиненные запросы могут играть важную роль, поскольку они дают возможность отбирать строки для обновления, опираясь на информацию из других таблиц. Например:

Всем служащим отделения Минского отделения увеличить зарплату на 10%

update staff

set salary=1.1*salary

WHERE bno IN (SELECT bno

FROM branch

WHERE city=‘Минск’);

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

Инструкция MERGE. Начиная с версии Oracle 9i, появилась возможность использовать инструкцию MERGE INTO, которая объединяет в себе возможности инструкций вставки, обновления и удаления, делая их более удобными для пользователей.

Общий синтаксис инструкции MERGE INTO:

MERGE INTO destination_table dest

USING (SELECT col1, col2, col3 FROM source_table) source1

ON (dest.col1 = source1.col1)

WHEN MATCHED THEN

UPDATE SET dest.col2 = source1.col2,

dest.col3 = source1.col3

WHERE source1.col2 IS NOT NULL

DELETE source1.col2 IS NULL

WHEN NOT MATCHED THEN

INSERT (dest.col1, dest.col2, dest.col3)

VALUES (source1.col1, source1.col2, source1.col3)

WHERE source1.col2 IS NOT NULL

Пример использования:

MERGE INTO copy_emp c

USING employees e

ON (c.employee_id = e.employee_id)

WHEN MATCHED THEN

UPDATE SET

c.first_name = e.first_name,

c.last_name = e.last_name,

c.department_id = e.department_id

WHEN NOT MATCHED THEN

INSERT VALUES(e.employee_id, e.first_name, e.last_name,

e.email, e.phone_number, e.hire_date, e.job_id,

e.salary, e.commission_pct, e.manager_id,

e.department_id);