DML. INSERT и UPDATE

На несколько уроков отвлечемся от извлечения данных и познакомимся с другими, не менее важными возможностями DML: вставка и обновление (в этом уроке), а также удаление данных из таблицы (в следующем).

Оператор INSERT

Казалось бы, со вставкой данных в таблицу мы уже знакомились в рамках Урока 84 (https://telegra.ph/DML-INSERT-07-02).

Но теперь, умея использовать подзапросы, мы можем использовать новый для нас способ – вставка данных на базе данных из другой таблицы.

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

Итак, создадим две новые таблицы (к DDL мы еще не приступили, поэтому просто копируем запросы):

create table passenger_male (
  id                   bigserial,
  first_name           varchar(100),
  last_name            varchar(100),
  birth_date           date,
  last_purchase        timestamp,
  favorite_airports    text[],
  created              timestamp      default now() 
);

create table passenger_female (
  id                   bigserial,
  first_name           varchar(100),
  last_name            varchar(100),
  birth_date           date,
  last_purchase        timestamp,
  favorite_airports    text[],
  created              timestamp      default now() 
);

Вы можете заметить, что исчезла колонка, отвечающая за пол – она теперь избыточна. Зато добавилась колонка created – для обозначения даты создания записи.

Таблицы есть, осталось их наполнить. Поскольку переносить данные в ручном режиме – задача трудоемкая и неблагодарная, сделаем это в автоматическом режиме (на примере passenger_male):

insert into passenger_male 
  (id, first_name, last_name, birth_date, last_purchase, 
    favorite_airports)
select id, first_name, last_name, birth_date, last_purchase,
    favorite_airports 
from passenger where male;

И вот таблица passenger_male уже заполнения пассажирами-мужчинами.

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

К слову, без явного указания колонок в INSERT тоже можно, но, тогда придется явно указывать время создания записи (значение для новой колонки). Заодно в примере ниже приведем все имена и фамилии к верхнему регистру (таблицу лучше пересоздать перед выполнением примера ниже. Или очистить, если сможете):

insert into passenger_male
select id, upper(first_name), upper(last_name), birth_date, 
    last_purchase, favorite_airports, now() 
from passenger where male;

Оператор UPDATE

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

С особенностями использования разберемся на базе примеров.

update passenger set first_name = upper(first_name);

Запрос на обновление начинается с оператора UPDATE, после которого указывается название таблицы, записи которой будут обновлены. Далее используется оператор SET, за которым следуют обновляемые колонки и присваиваемые им значения.

В данном случае мы обновляем все записи таблицы, присваивая им текущее значение колонки, но переведенное в верхний регистр.

Также может быть использован и какой-то заданный литерал:

update passenger set first_name = 'sthName';

Или значение другой колонки (или какое-то иное выражение):

update passenger set first_name = concat(first_name, ' ', last_name);

Все то же самое актуально и для обновления нескольких колонок за раз:

update passenger set 
  first_name = upper(first_name), 
  last_name = upper(last_name);

Но большинство задач, все же, не подразумевают обновления всех записей таблицы, требуя обновить лишь часть, отфильтрованную по каким-то признакам:

update passenger set last_name = 'Ivanoff' 
where lower(last_name) = 'ivanov';

В данном случае, всем пассажирам с фамилией 'ivanov' (записанной в любом регистре) будет установлена фамилия 'Ivanoff'.

WHERE-условие будет общим для всех обновляемых колонок, если их несколько.

Так же могут быть использованы подзапросы (как в SET-, так и в WHERE- блоках):

update passenger set birth_date = (
  select min (birth_date) from passenger
) 
where male;

update passenger set last_name = 'Ivanoff' 
where lower(last_name) = 'ivanov' 
  and birth_date = (select min(birth_date) from passenger); 

Как видите, синтаксически в операции обновления нет особенных сложностей – в целом, все действия достаточно логичны и имеют массу общих элементов с уже изученными SELECT-запросами. Это не отменяет того, что отдельные операции обновления могут стать целым квестом, но это зависит от конкретной БД, задачи и навыков исполнителя.

С теорией на сегодня все!

Переходим к практике:

Напоминаю, что практика в рамках данных статей остается формальной. Для закрепления навыков работы с SQL рекомендую использовать специализированные сервисы или любые иные источники задач.

Задача 1

Заполните таблицу passenger_female пассажирами-женщинами из таблицы passenger.

Задача 2

Обновите все записи в таблице passenger_female, установив им датой создания – 01.01.2023.

Задача 3

Смените имя всем пассажирам-женщинам с Katerina на Ekaterina. Записи с другим значением имени оставьте без изменений.

Если что-то непонятно или не получается – welcome в комменты к посту или в лс:)

Канал: https://t.me/ViamSupervadetVadens

Мой тг: https://t.me/ironicMotherfucker

Дорогу осилит идущий!