Как удалить значение в таблице sql

Как удалить значения из трёх связанных таблиц одновременно?

Суть проблемы: Есть 3 таблицы в MySQL связанные между собой ключами. По непонятной причине в заглавной таблице (Indicator) появились дублирующие записи, которые не нужны, но удалить их не получается, потому что к ним привязаны записи в monitoring_result, а к тем записи из monitoring_results_methods. Возможно ли каким-то образом удалить все записи monitoring_results_methods связанные с monitoring_result (и сами записи в monitoring_result), которые связанны с записью в Indicator в одном запросе? Сделать это руками физически невозможно — записей огромное количество.

Отслеживать
задан 27 июл 2020 в 12:22
navasiolau navasiolau
148 7 7 бронзовых знаков
Посмотрите Multi-Table Deletes. Также можете посмотреть тут
27 июл 2020 в 12:54

Поставьте на ключах связывающих таблицы ON DELETE CASCADE и при удалении записи из родительской таблицы автоматически будут удаляться все записи в дочерних

27 июл 2020 в 12:59
Собственно каскадное удаление прописано в сущностях symfony. Это не решает проблемы.
27 июл 2020 в 13:07

@Alexandr спасибо за ссылки, получилось удалить значения в monitoring_result_method указав id indicator, но как удалить еще и записи в monitoring_result ? Вот то что есть на данный момент: DELETE monitoring_results_methods FROM monitoring_results_methods INNER JOIN monitoring_result ON monitoring_result.id = monitoring_results_methods.result_id WHERE monitoring_result.indicator_id = 6424

27 июл 2020 в 13:09

@navasiolau DELETE indicator, monitoring_result, monitoring_result_method FROM indicator LEFT JOIN monitoring_result ON monitoring_result.indicator_id = indicator.id LEFT JOIN monitoring_result_method ON monitoring_result_method.result_id = monitoring_result.id WHERE indicator.id = 6424

SQL-Ex blog

Оператор DELETE позволяет удалить одну или более строк из таблицы, включая временные таблицы. В целом оператор DELETE является довольно простым, но он не менее необходим в вашем арсенале инструментов DML, наряду с другими операторами DML — SELECT, INSERT, UPDATE .

Подготовка среды MySQL

Как и в предыдущих статьях, я использовал в этой статье те же самые базу данных и таблицы (база данных travel и таблицы manufacturers и airplanes). В данном случае, однако, я рекомендую вам для простоты начать с нуля и перестроить базу данных и таблицы. Для установки базы данных выполните следующий скрипт:

DROP DATABASE IF EXISTS travel; 
CREATE DATABASE travel; 
USE travel; 
CREATE TABLE manufacturers ( 
manufacturer_id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
manufacturer VARCHAR(50) NOT NULL, 
create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
last_update TIMESTAMP NOT NULL 
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
PRIMARY KEY (manufacturer_id) ) 
ENGINE=InnoDB AUTO_INCREMENT=1001; 
CREATE TABLE airplanes ( 
plane_id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
plane VARCHAR(50) NOT NULL, 
manufacturer_id INT UNSIGNED NOT NULL, 
engine_type VARCHAR(50) NOT NULL, 
engine_count TINYINT NOT NULL, 
max_weight MEDIUMINT UNSIGNED NOT NULL, 
wingspan DECIMAL(5,2) NOT NULL, 
plane_length DECIMAL(5,2) NOT NULL, 
parking_area INT GENERATED ALWAYS AS ((wingspan * plane_length)) STORED, 
icao_code CHAR(4) NOT NULL, 
create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
last_update TIMESTAMP NOT NULL 
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
PRIMARY KEY (plane_id), 
CONSTRAINT fk_manufacturer_id FOREIGN KEY (manufacturer_id) 
REFERENCES manufacturers (manufacturer_id) ) 
ENGINE=InnoDB AUTO_INCREMENT=101;

После создания базы данных вы можете добавить тестовые данные, необходимые для работы с примерами статьи. Начните с выполнения следующего оператора INSERT, чтобы добавить данные в таблицу manufacturers:

INSERT INTO manufacturers (manufacturer) 
VALUES ('Bombardier'), ('Beagle Aircraft Limited'); 
SELECT * 
FROM manufacturers;

Оператор добавляет две строки в таблицу manufacturers и выводит эти строки. Столбцу manufacturer_id для этих строк должны были быть присвоены значения 1001 и 1002.

После подтверждения наличия данных в таблице manufacturers вы должны выполнить следующий оператор INSERT, который заполнит таблицу airplanes:

INSERT INTO airplanes 
(plane, manufacturer_id, engine_type, engine_count, 
wingspan, plane_length, max_weight, icao_code) 
VALUES 
('Learjet 24',1001,'Jet',2,35.58,43.25,13000,'LJ24'), 
('Learjet 24A',1001,'Jet',2,35.58,43.25,12499,'LJ24'), 
('Challenger (BD-100-1A10) 300',1001,'Jet',2,63.83,68.75,38850,'CL30'), 
('Challenger (BD-100-1A10) 350',1001,'Jet',2,69,68.75,40600,'CL30'), 
('Challenger (CL-600-1A11) 600',1001,'Jet',2,64.33,68.42,36000,'CL60'), 
('Challenger (CL-600-2A12) 601',1001,'Jet',2,64.33,68.42,42100,'CL60'), 
('A.109 Airedale',1002,'piston',1,36.33,26.33,2750,'AIRD'), 
('A.61 Terrier',1002,'piston',1,36,23.25,2400,'AUS6'), 
('B.121 Pup',1002,'piston',1,31,23.17,1600,'PUP'), 
('B.206',1002,'piston',2,55,33.67,7500,'BASS'), 
('D.4-108',1002,'piston',1,36,23.33,1900,'D4'), 
('D.5-108 Husky',1002,'piston',1,36,23.17,2400,'D5'); 
SELECT * 
FROM airplanes;

Значения 1001 и 1002 из таблицы manufacturers предоставляют значения внешнего ключа для столбца manufacturer_id в таблице airplanes. После выполнения второго оператора INSERT, запрос SELECT должен подтвердить, что 12 строк был вставлены в таблицу airplanes. Первой строке должно быть присвоено значение 101 в столбце plane_id, для остальных строк значения plane_id должны последовательно возрастать.

Синтаксис оператора DELETE

Базовый синтаксис оператора DELETE довольно прост и включает много тех же самых элементов, которые вы видели в других операторах DML:

DELETE [IGNORE] FROM table_name 
[WHERE where_condition] 
[ORDER BY order_list] 
[LIMIT row_count]

• Предложение DELETE, которое включает подчиненное предложение FROM, является единственным обязательным предложением в операторе DELETE. Это предложение задает таблицу, из которой будут удаляться данные. Вы можете указать несколько таблиц, что включает определение условий соединения, но я сфокусируюсь в этой статье на удалении из одной таблицы. Предложение DELETE также поддерживает использование модификатора IGNORE для возвращения предупреждающих сообщений, а не ошибок при возникновении проблем.
• Предложение WHERE определяет, какие строки удалять на основе одного или более поисковых критериев. Предложение работает во многом подобно предложению WHERE в операторах SELECT и UPDATE. Хотя предложение WHERE не является обязательным, следует быть очень осторожным при выполнении оператора DELETE, который его не содержит. Без предложения WHERE оператор удалит каждую строку в целевой таблице, если не включено предложение LIMIT.
• Предложение ORDER BY задает порядок, в котором должны удаляться строки. Это предложение используется, главным образом, в сочетании с предложением LIMIT, чтобы точнее указать, какие строки следует удалить. Предложение ORDER BY подобно аналогичному предложению в операторах SELECT и UPDATE. Это предложение не является обязательным и не может использоваться для удаления из нескольких таблиц.
• Предложение LIMIT ограничивает число удаляемых строк. При использовании с предложением ORDER BY удаляемые строк будут определяться порядком сортировки, заданной этим предложением. Предложение LIMIT не является обязательным и не может использоваться для удаления из нескольких таблиц.

Удаление данных из таблицы MySQL

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

DELETE FROM airplanes;

Оператор удалит все данные из таблицы airplanes, если не включен режим безопасности. Режим безопасности обычно включен по умолчанию в экземпляре MySQL, чтобы ограничить возможность обновления или удаления данных в таблице.

Как и для оператора UPDATE, если режим безопасности включен в вашем экземпляре MySQL, вы получите следующее сообщение об ошибке при попытке выполнить приведенный выше оператор:

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.
(Вы используете режим безопасного обновления и пытаетесь обновить таблицу без предложения WHERE, которое использует ключевой столбец. Для отключения безопасного режимы переключите опцию в Preferences -> SQL Editor и повторно соединитесь.)

Когда безопасный режим включен, вы не можете выполнять оператор DELETE без предложения WHERE, которое не задает ключевой столбец в критериях оператора DELETE, если не включено предложение LIMIT. Это гарантирует, что вы не сможете непреднамеренно удалить все данные из таблицы. Вы можете выключить безопасный режим (как говорится в сообщении об ошибке), установив свойства сервера или временно, когда вы выполняете оператор DELETE. Временный подход обычно является наиболее безопасным.

Чтобы временно выключить безопасный режим, используйте оператор SET для изменения значения системной переменной SQL_SAFE_UPDATES на 0 до выполнения оператора DELETE, а затем установкой переменной в 1 после выполнения оператора, как показано в следующем примере:

SET SQL_SAFE_UPDATES = 0; 
DELETE FROM airplanes; 
SET SQL_SAFE_UPDATES = 1;

Операторы SET выключают системную переменную, а затем включают во время текущей сессии. При этом имейте в виду, что если оператор DELETE генерирует ошибку, выполнение оператора прерывается, и второй оператор SET не будет выполнен. Поэтому убедитесь, что вы выполнили этот оператор, чтобы сбросить переменную SQL_SAFE_UPDATES в 1. Заметим также, что оператор SET поддерживает необязательный модификатор GLOBAL, который определяет переменную на глобальном уровне. Однако я рекомендую вам не использовать эту опцию при отключении безопасного удаления. Менее рисковано отключать режим безопасности на уровне сессии, чтобы избежать непреднамеренных модификаций данных. Используйте GLOBAL, только если это существенно в вашей ситуации.

Если безопасный режим выключен, выполнение оператора DELETE не должно вызывать ошибки. После выполнения вы можете подтвердить изменения с помощью простого оператора SELECT, который вернет все данные из таблицы airplanes. Этот оператор не должен возвращать строк.

Добавление предложения WHERE в оператор DELETE

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

INSERT INTO airplanes 
(plane, manufacturer_id, engine_type, engine_count, 
wingspan, plane_length, max_weight, icao_code) 
VALUES 
('Learjet 24',1001,'Jet',2,35.58,43.25,13000,'LJ24'), 
('Learjet 24A',1001,'Jet',2,35.58,43.25,12499,'LJ24'), 
('Challenger (BD-100-1A10) 300' 
,1001,'Jet',2,63.83,68.75,38850,'CL30'), 
('Challenger (BD-100-1A10) 350' 
,1001,'Jet',2,69,68.75,40600,'CL30'), 
('Challenger (CL-600-1A11) 600' 
,1001,'Jet',2,64.33,68.42,36000,'CL60'), 
('Challenger (CL-600-2A12) 601' 
,1001,'Jet',2,64.33,68.42,42100,'CL60'), 
('A.109 Airedale',1002,'piston',1,36.33,26.33,2750,'AIRD'), 
('A.61 Terrier',1002,'piston',1,36,23.25,2400,'AUS6'), 
('B.121 Pup',1002,'piston',1,31,23.17,1600,'PUP'), 
('B.206',1002,'piston',2,55,33.67,7500,'BASS'), 
('D.4-108',1002,'piston',1,36,23.33,1900,'D4'), 
('D.5-108 Husky',1002,'piston',1,36,23.17,2400,'D5'); 
SELECT * 
FROM airplanes;

Подтвердите, что данные были добавлены в таблицу airplanes, проверив вывод оператора SELECT. Таблица должна теперь содержать те же 12 строк с одним заметным отличием. Первое значение plane_id теперь равно 113, а не 101, поскольку MySQL отслеживает последнее автоинкрементируемое значение, присвоенное строке, даже если эта строка была удалена.

После вставки данных в таблицу airplanes вы можете выполнить следующий оператор DELETE, который содержит базовое предложение WHERE:

DELETE FROM airplanes 
WHERE icao_code = 'pup';

Условие поиска указывает, что значение icao_code должно равняться pup для удаляемых строк. Однако, если попытаться выполнить этот оператор, и безопасный режим включен, MySQL снова вернет ошибку 1175:

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.
(Вы используете безопасный режм обновления и пытаетесь обновить таблицу без предложения WHERE, которое использует ключевой столбец. Для выключения безопасного режима переключите опцию в Preferences -> SQL Editor и повторно соединитесь.)

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

SET SQL_SAFE_UPDATES = 0; 
DELETE FROM airplanes 
WHERE icao_code = 'pup'; 
SET SQL_SAFE_UPDATES = 1;

Если выполнить запрос к таблице airplanes после выполнения этих операторов, в таблице теперь окажется только 11 строк, а не 12. Одна строка удовлетворяла условиям поиска, поэтому только она и была удалена.

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

DELETE FROM airplanes 
WHERE plane_id = 121;

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

SET SQL_SAFE_UPDATES = 0; 
DELETE FROM airplanes 
WHERE engine_type = 'piston' AND max_weight < 2500 ;
SET SQL_SAFE_UPDATES = 1;

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

Предложение WHERE включает два условия поиска. Первое указывает, что значение engine_type должно быть piston, а второе означает, что max_weight должно быть меньше 2500. Условия поиска соединяются логическим оператором AND, который означает, что оба условия должны быть истинны для удаляемых строк.

В этом случае несколько строк удовлетворяют обоим условиям поиска, поэтому они все были удалены из таблицы airplanes. Если выполнить запрос к таблице, можно убедиться, что она теперь содержит только восемь строк.

Добавление предложений ORDER BY и LIMIT в оператор DELETE

Предложения ORDER BY и LIMIT вместе помогают лучше управлять удалением строк из таблицы. Для того, чтобы увидеть их работу, начнем с возвращения строки в таблицу airplanes:

INSERT INTO airplanes 
(plane, manufacturer_id, engine_type, engine_count, 
wingspan, plane_length, max_weight, icao_code) 
VALUES 
('D.4-108',1002,'piston',1,36,23.33,1900,'D4');

Поскольку эта строка добавляется позже по времени, когда были добавлены предыдущие строки, она будет иметь отличное от других строк значение create_date. (Значение — временная метка.) Вы можете использовать это значение, чтобы выделить эту строку при построении оператора DELETE:

DELETE FROM airplanes 
ORDER BY create_date DESC 
LIMIT 1;

Обратите внимание, что оператор не включает предложения WHERE с ключевым столбцом, и что отсутствуют операторы SET. Вы можете так поступить здесь, поскольку оператор DELETE включает предложение LIMIT. При необходимости вы можете включить предложение WHERE с предложениями ORDER BY и LIMIT, но в этом нет необходимости в данном случае.

Предложение ORDER BY определяет, что строки должны удаляться на основе значений create_date, отсортированным в убывающем порядке. Это гарантрует, что последняя вставленная строка является первой удаляемой строкой в предположении уникальности значений create_date. Предложение LIMIT затем указывает, что удаляться должна только одна строка. Это будет первая строка, как определено в предложении ORDER BY. Вы можете применить этот прием при удалении архивных данных, за тем исключением, что указываться в предложении LIMIT будет не 1, а значения 100, 1000 или т.п.

Сложно сказать, насколько часто вы будете использовать предложения ORDER BY и LIMIT в ваших запросах. Но полезно знать, что у вас есть такой вариант, если вы хотите применить подобную логику при удалении данных из таблиц вашей базы данных.

Вы можете также использовать эти предложения независимо друг от друга. Например, вы можете использовать предложение LIMIT, если вам требуется удалить большое число строк из таблицы, и вы беспокоитесь о влиянии удаления на производительность базы данных. Вместо удаления сразу всех строк вы можете удалять их пакетами на основе числа строк, указанного в предложении LIMIT. Затем вы можете просто повторять оператор DELETE, пока не будут удалены все целевые строки.

Использование модификатора IGNORE в операторе DELETE

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

Чтобы увидеть как это работает, начнем с выполнения следующего оператора INSERT, который добавляет несколько строк в таблицу manufacturers:

INSERT INTO manufacturers (manufacturer) 
VALUES ('Airbus'), ('Beechcraft'), ('Cessna'), ('Piper'); 
SELECT * 
FROM manufacturers;

В выводе этого пакета вы должны обнаружить, что строка Airbus имеет значение manufacturer_id, равное 1003. Вы будете использовать это значение как внешний ключ при добавлении строки в таблицу airplanes для самолета Airbus. Чтобы добавить эту строку, выполните следующий оператор INSERT:

INSERT INTO airplanes 
(plane, manufacturer_id, engine_type, engine_count, 
wingspan, plane_length, max_weight, icao_code) 
VALUES 
('A220-100',1003,'Jet',2,115.08,114.75,134000,'BCS1');

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

DELETE FROM manufacturers 
WHERE manufacturer_id IN (1003, 1004, 1005, 1006);

Когда вы пытаетесь выполнить этот оператор, MySQL прерывает его выполнение и возвращает следующую ошибку, которая указывает на нарушение внешнего ключа:

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`travel`.`airplanes`, CONSTRAINT `fk_manufacturer_id` FOREIGN KEY (`manufacturer_id`) REFERENCES `manufacturers` (`manufacturer_id`))

MySQL вернула эту ошибку, поскольку вы пытались удалить строку, на которую ссылалась таблица airplanes. В результате весь оператор потерпел неудачу, и никакие строки не были удалены. Однако вы можете гарантировать продолжение выполнения оператора DELETE, даже если одно из удалений сбоит, включив модификатор IGNORE:

DELETE IGNORE FROM manufacturers 
WHERE manufacturer_id IN (1003, 1004, 1005, 1006);

Теперь оператор возвращает следующее сообщение, которое показывает число строк, которое было обработано, и предупреждение:

3 row(s) affected, 1 warning(s): 1451 Cannot delete or update a parent row: a foreign key constraint fails (`travel`.`airplanes`, CONSTRAINT `fk_manufacturer_id` FOREIGN KEY (`manufacturer_id`) REFERENCES `manufacturers` (`manufacturer_id`))

Из этого сообщения следует, что три строки были удалены, и что имелось нарушение ограничения внешнего ключа. Если обратиться к таблице manufacturers, вы обнаружите, что она содержит теперь только три строки, включая одну для Airbus. Чтобы удалить все данные Airbus, вы должны сначала удалить любые ссылающиеся записи из таблицы airplanes:

DELETE FROM airplanes 
WHERE manufacturer_id = 1003;

Затем вы сможете выполнить следующий оператор DELETE для удаления записи Airbus из таблицы manufacturers, в которой останется только две строки:

DELETE FROM manufacturers 
WHERE manufacturer_id = 1003;

Модификатор IGNORE может оказаться полезным, когда вам нужно выполнять удаление по расписанию, и вы хотите, чтобы оператор продолжал выполяться, даже если некоторые строки не могут быть удалены. Это особенно полезно при удалени больших наборов данных. После удаления основной массы записей вы можете вернуться к рассмотрению предупреждений. Имейте в виду, что модификатор IGNORE работает только для ошибок определенных типов.

Работа с оператором DELETE в MySQL

Наряду с операторами SELECT, INSERT и UPDATE, оператор DELETE представляет наиболее важный оператор, который используется для работы с данными MySQL. Однако это не все операторы DML. MySQL также поддерживает такие операторы DML, как CALL, LOAD DATA, REPLACE и TABLE.

Но рассмотренные в этой серии четыре оператора отлично подходят для начала работы с данными, при этом оператор DELETE завершает картину. Этот оператор делает возможным легко удалять данные, являющиеся некорректными или устаревшими. Фактически оператор слишком легок в использовании, и вы должны быть осторожны, чтобы непреднамеренно не удалить данные по ошибке. Тем не менее, оператор DELETE чрезвычайно полезен, и вы должны быть уверены, что вполне понимаете, как его использовать наряду с операторами SELECT, INSERT и UPDATE.

Обратные ссылки

Нет обратных ссылок

Комментарии

Показывать комментарии Как список | Древовидной структурой

Transact-SQL — изменение и удаление данных

Инструкция UPDATE используется для модифицирования строк таблицы. Эта инструкция имеет следующую общую форму:

Строки таблицы tab_name выбираются для изменения в соответствии с условием в предложении WHERE. Значения столбцов каждой модифицируемой строки изменяются с помощью предложения SET инструкции UPDATE, которое соответствующему столбцу присваивает выражение (обычно) или константу. Если предложение WHERE отсутствует, то инструкция UPDATE модифицирует все строки таблицы. С помощью инструкции UPDATE данные можно модифицировать только в одной таблице.

В примере ниже инструкция UPDATE изменяет всего лишь одну строку таблицы Works_on, поскольку комбинация столбцов EmpId и ProjectNumber является первичным ключом этой таблицы и, следственно, она однозначна. В данном примере изменяется должность сотрудника, значение которого было ранее неизвестно или имело значение NULL:

USE SampleDb; UPDATE Works_on SET Job = 'Менеджер' WHERE EmpId = 9031 AND ProjectNumber = 'p3';

В примере ниже значения строкам таблицы присваиваются посредством выражения. Запрос пересчитывает бюджеты всех проектов с долларов на евро:

USE SampleDb; UPDATE Project SET Budget = Budget * 0.9;

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

В примере ниже в предложении WHERE инструкции UPDATE используется вложенный запрос. Поскольку применяется оператор IN, то этот запрос может возвратить более одной строки:

USE SampleDb; UPDATE Works_on SET Job = NULL WHERE EmpId IN (SELECT Id FROM Employee WHERE LastName = 'Вершинина');

Согласно этому запросу, для сотрудницы Вершининой Натальи во всех ее проектах в столбце ее должности присваивается значение NULL. Запрос в этом примере можно также выполнить посредством предложения FROM инструкции UPDATE. В предложении FROM указываются имена таблиц, которые обрабатываются инструкцией UPDATE. Все эти таблицы должны быть в дальнейшем соединены. Применение предложения FROM показано в примере ниже. Логически, этот пример идентичен предыдущему:

USE SampleDb; UPDATE Works_on SET Job = NULL FROM Works_on, Employee WHERE LastName = 'Вершинина' AND Works_on.EmpId = Employee.Id;

В примере ниже показано использование выражения CASE в инструкции UPDATE. (Подробное рассмотрение этого выражения описывалось ранее.) В данном примере нужно увеличить бюджет всех проектов на определенное число процентов (20, 10 или 5), в зависимости от исходной суммы бюджета: чем меньше бюджет, тем больше должно быть его процентное увеличение:

USE SampleDb; UPDATE Project SET Budget = CASE WHEN Budget > 0 AND Budget 100000 AND Budget < 150000 THEN Budget * 1.1 ELSE Budget * 1.05 END;

Инструкция DELETE

Инструкция DELETE удаляет строки из таблицы. Подобно инструкции INSERT, эта инструкция также имеет две различные формы:

Удаляются все строки, которые удовлетворяют условие в предложении WHERE. Явно перечислять столбцы в инструкции DELETE не то чтобы нет необходимости, а даже не разрешается, поскольку эта инструкция оперирует строками, а не столбцами. Использование первой формы инструкции DELETE показано в примере ниже, в котором происходит удаление из таблицы Works_on всех сотрудников с должностью 'Менеджер':

USE SampleDb; DELETE FROM Works_on WHERE Job = 'Менеджер';

Предложение WHERE инструкции DELETE может содержать вложенный запрос, как это показано в примере ниже:

USE SampleDb; DELETE FROM Works_on WHERE EmpId IN (SELECT Id FROM Employee WHERE LastName = 'Вершинина'); DELETE FROM Employee WHERE LastName = 'Вершинина';

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

USE SampleDb; DELETE Works_on FROM Works_on w, Employee e WHERE w.EmpId = e.Id AND LastName = 'Вершинина'; DELETE FROM Employee WHERE LastName = 'Вершинина';

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

USE SampleDb; -- Удаление всех строк таблицы DELETE FROM Works_on;

Инструкции DELETE и DROP TABLE существенно отличаются друг от друга. Инструкция DELETE удаляет (частично или полностью) содержимое таблицы, тогда как инструкция DROP TABLE удаляет как содержимое, так и схему таблицы. Таким образом, после удаления всех строк посредством инструкции DELETE таблица продолжает существовать в базе данных, а после выполнения инструкции DROP TABLE таблица больше не существует.

Другие инструкции и предложения Transact-SQL для модификации таблиц

Сервер SQL Server поддерживает следующие дополнительные инструкции и предложения для модификации таблиц:

• инструкцию TRUNCATE TABLE;
• инструкцию MERGE;
• предложение OUTPUT.

Эти инструкции и предложение рассматриваются в последующих подразделах.

Инструкция TRUNCATE TABLE

Инструкция TRUNCATE TABLE является более быстрой версией инструкции DELETE без предложения WHERE. Эта инструкция удаляет все строки таблицы более быстро, чем инструкция DELETE, поскольку она удаляет содержимое постранично, тогда как инструкция DELETE делает это построчно. Инструкция TRUNCATE TABLE является расширением Transact-SQL стандарта SQL. Еще одним важным отличием этой инструкции является то, что она сбрасывает индекс столбца, для которого указано свойство автоинкремента IDENTITY.

Инструкция TRUNCATE TABLE имеет следующий синтаксис:

TRUNCATE TABLE table_name 

Инструкция MERGE

Инструкция MERGE объединяет последовательность инструкций INSERT, UPDATE и DELETE в одну элементарную инструкцию, в зависимости от существования записи (строки). Иными словами, можно синхронизировать две разные таблицы, чтобы модифицировать содержимое таблицы назначения в зависимости от различий, обнаруженных в таблице-источнике.

Основной областью применения для инструкции MERGE является среда хранилищ данных, где таблицы необходимо периодически обновлять, чтобы отражать новые данные, прибывающие с систем оперативной обработки транзакций OLTP (On-Line Transaction Processing). Эти данные могут содержать изменения существующих строк таблиц и/или новый строки, которые нужно вставить в таблицы. Если строка в новых данных соответствует записи, которая уже имеется в таблице, выполняется инструкция UPDATE или DELETE. В противном случае выполняется инструкция INSERT.

Альтернативно, вместо инструкции MERGE можно использовать последовательность инструкций INSERT, UPDATE и DELETE, в которых для каждой строки решается, какую операцию выполнять: вставку, удаление или обновление. Но этот подход имеет значительный недостаток, связанный с производительностью: в нем требуется выполнять несколько проходов по данным, а данные обрабатываются по принципу "запись за записью".

Предложение OUTPUT

По умолчанию единым видимым результатом выполнения инструкции INSERT, UPDATE или DELETE является только сообщение о количестве модифицированных строк, например "3 rows DELETED" (удалены 3 строки) и система не сохраняет информацию о модифицированных данных. Если такой видимый результат не удовлетворяет вашим требованиям, то можно использовать предложение OUTPUT, которое выводит модифицированные, вставленные или удаленные строки.

Предложение OUTPUT также применимо с инструкцией MERGE, для которой оно выводит все модифицированные строки в виде таблицы.

Результаты выполненных операций соответствующих инструкций предложение OUTPUT выводит в таблицах inserted и deleted. Кроме этого, чтобы заполнить таблицы, в предложении OUTPUT требуется использовать выражение INTO. Поэтому для сохранения результата используется табличная переменная.

В примере ниже показано использование инструкции OUTPUT с инструкцией DELETE:

USE SampleDb; DELETE FROM Works_on; -- В эту переменную будут сохраняться удаленные данные DECLARE @deleteTable TABLE (Id INT, LastName NCHAR(20)); DELETE Employee OUTPUT deleted.Id, deleted.LastName INTO @deleteTable WHERE Id > 12000; SELECT * FROM @deleteTable;

При условии, что содержимое таблицы находится в исходном состоянии, выполнение запроса в примере дает следующий результат:

В этом примере сначала объявляется табличная переменная @deleteTable с двумя столбцами: Id и LastName. В этой таблице будут сохранены удаленные строки. Синтаксис инструкции DELETE расширен предложением OUTPUT: "OUTPUT deleted.Id, deleted.LastName INTO @deleteTable". Посредством этого предложения система сохраняет удаленные строки в таблице deleted, содержимое которой потом копируется в переменную @deleteTable.

В примере ниже показано использование предложения OUTPUT в инструкции UPDATE:

USE SampleDb; -- Перед запуском этого примера, нужно -- будет восстановить исходные данные в базе DECLARE @updateTable TABLE (Id INT, ProjectNumber NCHAR(20), oldJob NCHAR(15), newJob NCHAR(15)); UPDATE Works_on SET Job = 'Менеджер' OUTPUT deleted.EmpId, deleted.ProjectNumber, deleted.Job, inserted.Job INTO @updateTable WHERE Job = 'Консультант'; SELECT Id, ProjectNumber, oldJob 'Старая работа', newJob 'Новая работа' FROM @updateTable;

Результат выполнения этого запроса:

Как удалить значение в таблице sql

1. Перетащите действие Удалить строки из списка в соединителе Соединитель SQL на канву, наведите указатель на это действие и щелкните значок или дважды щелкните действие.

Откроется окно "Удалить строки".

2. При необходимости измените поле Метка . По умолчанию наименование метки совпадает с наименованием действия.

3. В списке База данных выберите тип базы данных, с которой требуется соединиться.
◦ PostgreSQL
◦ SQL Server
Если соединение для соединителя SQL было добавлено ранее, выберите соединение из списка.
5. В списке Таблица выберите нужную таблицу базы данных, из которой следует удалить строки.
За один раз можно удалить несколько строк.
6. В группе Где в списке Соединить предложения с помощью выберите одну из следующих опций:
◦ И - все условия, указанные в группе Атрибуты , должны возвращать значение "истина".
◦ ИЛИ - хотя бы одно из условий, указанных в группе Атрибуты , должно возвращать значение "истина".
Если не выбрано ни одно условие в группе Где , действие удалит все строки в выбранной таблице.
7. В группе Атрибуты щелкните Добавить и выполните следующие действия.
a. В списке Атрибут выберите столбец, который требуется фильтровать.
b. В списке Операторы выберите соответствующий условный оператор.
c. В поле Значение введите значение столбца для фильтра.

Щелкайте Добавить , чтобы добавить несколько атрибутов в оператор объединения. Можно также щелкать значок , чтобы удалять атрибуты.

Если добавляются одни и те же атрибуты и выбирается И в списке Соединить предложения с помощью , атрибуты объединяются оператором ИЛИ .

8. Нажмите кнопку Готово .
Схема вывода

Схема вывода для действия Удалить строки возвращает строку сообщения и число строк, которые были удалены.