MERGE есть не во всех СУБД, да и подзапросы могут негативно сказаться на производительности. Хорошим решением вставки строк из одной таблицы в другую с предварительной проверкой отсутствия вставляющихся записей, является классический LEFT JOIN с проверкой во WHERE.
Данный материал, по большей части, думаю, будет полезен специалистам службы внедрения и поддержки пользователей с техническим разбором возникших проблем, так как этим горячим головам на боевой базе в реальном времени приходится решать проблемы клиента. И когда им не получается самостоятельно что-то сделать в базе данных, на помощь приходят уже программисты (или другие специалисты с более серьёзным опытом работы с базами данных). Я и есть тот самый программист, которого подключают к решению сложных технических проблем. И целых несколько раз за последние пол года помогал поддержке с импортом данных из одной таблицы в другую с предварительной проверкой на отсутствие импортируемых записей в целевой таблице, заодно объясняя классику. Потому и решил написать данную статью.
Для начала давай вспомним как написать простейшую команду вставки одной строки в таблицу. Чтобы добавить строчку в некоторую таблицу, нужно воспользоваться командой INSERT:
Заместо слова Таблица нужно писать имя таблицы, в которую вставляется новая строка, заместо "Столбец1", "Столбец2" и тд. указываем названия столбцов которые собираемся заполнять для вставляющейся строки. Часто для новой вставляющейся строки не обязательно заполнять значения всех столбцов. Можно заполнить только нужные столбцы добавляемой строки. Те столбцы, которые не указано чем заполнять при вставке новой строки, останутся с пустыми значениями (или с другими значениями, если это указано в настройках столбцов таблицы).
Например, командой:
будет вставлена новая строчка в таблицу ANALYTICAL_CODES. Столбец ID_CODE у этой новой строки будет заполнен значением 108, а VALUE_CODE текстом 'Финансирование рекламы'.
ORACLE и другие СУБД поддерживают вставку стазу нескольких новых строк в таблицу. Значения для указанных в команде INSERT столбцов можно подготовить SELECT-ом. Чтобы это сделать, нужно, как и в предыдущем примере, написать:
А дальше мы можем не вручную писать в команду INSERT вставляемые в столбцы новых строк значения, а сформировать эти значения запросом. SELECT подготовит набор (таблицу) данных, которые разом будут вставлены в целевую таблицу. Сколько строк подготовит SELECT, столько строк и будет вставлено в целевую таблицу. Это называется INSERT на основе SELECTа. К тому же, если SELECT не подготовит (не выберет из таблицы-источника) ни одной строки, то команда INSERT ни одной строки и не вставит. Команда SELECT должна выдать таблицу, в которой столбцы будут иметь подходящие значения для указанных в блоке INSERT столбцов и столбцы должны быть перечислены в том же порядке что и в блоке INSERT. Например, если нам нужно было бы вставить в таблицу "Неактивных клиентов" строчки из таблицы "Клиентов" (из таблицы всех клиентов), подходящих по некоторым условиям, попутно заполняя столбец DATE_INACTIVE текущим числом (так как именно сегодня клиент будет занесён как неактивный), то мы могли бы сделать это так:
В таблицу "Неактивных клиентов" будут занесены те клиенты, у которых в графе "Статус" указано значение 9 и дата последнего контакта с ним была более года назад. Командой SELECT мы подготовили набор данных из четырёх столбцов, как раз столько же столбцов и ожидает INSERT. Первое значение, что подготовит SELECT, будет вставлено в столбец ID_CLIENT, второе значение будет вставлено в столбец NAME_CLIENT, третье - в столбец DATE_REG, и значение последнего столбца будут вставляться в столбец целевой таблицы с именем DATE_INACTIVE.
Команда INSERT определяет то, какие столбцы и в каком порядке будут заполняться, а команда SELECT должна подготавливать (выбирать) необходимые данные со столбцами в этом же порядке. Если SELECT подготовит другое количество столбцов данных или какой-либо столбец будет иметь отличный тип данных, от того, который ожидает INSERT, то вставка не будет выполнена. Будет ошибка.
Если мы выполним предыдущую команду вставки записей в таблицу "Неактивных клиентов" ещё раз, то, скорее всего, мы можем получить ошибку, что какие-то из клиентов (клиенты с таким ID_CLIENT) уже есть в целевой таблице. В таблице "Неактивных клиентов" первичным ключом является столбец ID_CLIENT, а значит строки с одинаковым значением в этом столбце недопустимым.
Теперь посмотрим как можно, при вставке в таблицу "Неактивных клиентов" записей из таблицы "Клиентов", сделать ещё проверку на отсутствие их с таким же ID_CLIENT. Чтобы, при повторном запуске этого скрипта, если некоторые клиенты уже числятся в таблице "Неактивных клиентов", чтобы они не были выбраны SELECT-ом. И, соответственно, они не будут вставлены. Эту задачу можно решить несколькими способами. Например, с помощью NOT EXISTS в блоке WHERE.
Сначала выберем строки из таблицы "Клиентов" которые подходят под условия добавления в таблицу "Неактивных клиентов", но ещё в таблице "Неактивных клиентов" отсутствующие:
Вышенаписанный запрос выберет новых "бывших" клиентов. Именно таких, которые подходят под условия переноса в таблицу неактивных, но ещё там не значащихся. Осталось выше нашей команды SELECT написать фразу INSERT и подготовленные (выбранные) данные будут сразу вставлены:
И теперь, наконец, я покажу как можно выбрать подходящие данные с проверкой на отсутствие их в целевой таблице (для последующей их вставки), без использования подзапроса с NOT EXISTS. Выберем подходящие данные как и раньше:
И теперь к каждой выводящейся строки приджоиним клиента с таким же идентификатором из таблицы "Неактивных клиентов". Если, конечно, он там (уже) есть. Ты спросишь, наверное, зачем же нам его джоинить? Вот как раз для проверки наличия его в таблице неактивных и нужно попробовать его приджоинить. Если найдётся что приджоинить, значит клиент в таблице неактивных уже есть. И нам таких как раз не нужно. Наш запрос должен выбрать клиентов, которым не нашлось строк в таблице неактивных. Будем использовать LEFT JOIN:
Присоединили к выводимым клиентам их же из таблицы неактивных. Даже можно в блоке SELECT пока вывести какой-нибудь столбец из присоединённой таблицы. Так, выполнив запрос и получив результирующую таблицу данных, мы сможем визуально увидеть к какому клиенту присоединилась запись о нем же из таблицы неактивных:
Конечно, в последнем столбце, в случае наличия у клиента записи о нем же в таблице "неактивных клиентов", будет выводиться то же значение, что и в первом столбце ID_CLIENT. Это так, но зато если вдруг для какого-либо клиента в этом столбце ничего не будет выведено (будет пустая ячейка - NULL), то это будет означать, что у клиента нет соответствие в таблице неактивных клиентов. А нам как раз такие и нужны! Нужно вывести таких клиентов, у которых в этом столбце было бы пусто (то есть NULL). Если не все смог понять с первого раза, то прочти, пожалуйста, хотя бы последний абзац ещё раз.
Итак, наш запрос будет дополнен условием на общую получаемую таблицу данных условием во WHERE:
Наш запрос, выбирающий подходящих клиентов для занесены их в таблицу "Неактивных клиентов", но ещё в ней отсутствующих, готов! Столбец i.ID_CLIENT в блоке SELECT нам нужен был только для визуального контроля выбираем данных и теперь его можно убрать. С помощью него мы смогли очно убедиться в правильности подготовленных для вставки (выбранных) данных. Уберём его из блока SELECT, но, разумеется, оставим во WHERE. Допишем INSERT сверху и наша работа завершена!
Буду рад, если ты все смог понять и если найдёшь эту статью интересной и полезной. Поставь, пожалуйста, лайк или поделись с другом!
Если не все смог понять с первого раза, то прочти, пожалуйста, ещё один раз. Это нормально не все смочь уловить из нового материала с первого раза. После второго раза, возможно, сможешь поймать ещё что-то, что не смог уловить с первого.
Этот приём позволит решать тебе в будущем некоторые сложные задачи простым и классическим способом, где это, разумеется, применимо!
Спасибо тебе за прочтение!
Заходи на наш Youtube-канал и найдёшь много интересного и полезного про базы данных и язык SQL.
А здесь ты можешь пройти наш курс обучения языку SQL и получить мощные знания и умения! Всего за 27 уроков до уровня специалиста, имеющего трёхлетний стаж. Много практики в реальной базе данных! С нашей поддержкой и проверкой твоих самостоятельный задач! Записывайся, буду тебя ждать!