Сложный Update с использованием нескольких таблиц в Oracle 11 R2

Всем привет!

Хотел бы написать сегодня про «сложный» апдейт данных в Oracle с помощью нескольких таблиц. Все разработчики БД знают про то, как обновлять данные, это одно из самых первых, что все изучают. Я не буду в этой статье лить воду, те, кто мало знаком с апдейтом в принципе, думаю, необходимо прочитать что это такое и с чем это едят, а тем кто уже считает себя опытным — прошу! Итак, самый простой update выглядит так:

UPDATE table1
SET column1='Новосибирск'
WHERE column1='Сахалин'

Но что делать, когда нужно обновить данные в одной таблице, исходя из данных в другой таблице? Хм..давайте сделаем это 🙂  Недавно, на работе, увидел как коллега запустил обновление таблички, посмотрев через специальный скрипт (напишу потом отдельно по этому скрипту статью), что он делает, какой у него код и самое главное — сколько скрипт будет выполняться (да, oracle может показывать время окончания из специальных служебных таблиц) я просто офигел — выполнения скрипта должно окончиться, эдак через 5 дней 🙂 Виной всему неоптимизированный алгоритм выполенения апдейта. Итак, приступим.

Допустим, у нас существуют 2 таблички, у которых есть одинаковое поле для объединения (обязательное условие) , например, номер договора. В таблице 1 также есть адрес магазина и клиента, а в таблице 2 тоже какая-нибудь инфа и более корректные адреса магазина и адреса клиентов (можете что-то свое придумать). В итоге нам нужно из 2 таблицы взять эти корректные адреса магазинов и клиентов и подставить в 1 таблицу. Пишем запрос:

UPDATE
(
SELECT
       t.adress_shop,
       t.adress_client,
       s.adress_shop AS adress_shop_new,
       s.adress_client AS adress_client_new
 
FROM table1 t
INNER JOIN table2 s ON s.ur_number=t.ur_number
)
tt
SET tt.adress_shop=tt.adress_shop_new, tt.adress_client=tt.adress_client_new

Я думаю вы тут все поняли, что и как. Немного поясню — выбираем поля, которые нужно обновить из первой таблицы, также тут же выбираем из второй таблицы, на которые нужно обновить. Все это добро цепляем по номеру договору (не забываем про дубликаты). Также тут специально использовал одинаковые название полей, чтобы хоть как-то усложнить, сделал 2 поля для обновления после «set», а то просто возникал как-то вопрос, как обновить одновременно 2 поля.

Здесь я использовал «inner join», но можете любой join  — кому как угодно, в зависимости от посталенной задачи и производительности. Соответсвенно, в join’е можно использовать несколько таблиц. Думаю, все знают как джоиниться к другим таблицам, это описывать я не буду, в любой книге найти можно. Я лишь хотел показать то, что уже не в каждой книге найдешь. Когда начинал работать с Oracle вставали у меня вопросы такого апдейта, мне в свое время помогли, когда был начинающий, теперь хочу помочь вам 🙂 Кстати, есть еще один способ сделать такой же апдейт, но через «merge» — это оператор для «insert», «delete» и «update». Напишу скрипт с небольшими пояснениями, если будет что непонятно — всегда можете задать вопрос:

MERGE INTO table1 t
 
USING (SELECT * FROM table2 s) p
  ON (p.ur_number=t.ur_number)
 
WHEN MATCHED THEN--когда совпали договора, то идет сам update
 
UPDATE SET t.adress_shop=p.adress_shop, t.adress_client=p.adress_client
 
WHEN NOT MATCHED THEN INSERT (t.adress_shop) VALUES ('Ничего не нашел')

Кстати, 2 вариант считается уже более высокого уровня. Он показывает, что вы знакомы со сложным апдейтом, а также умеете работать с оператором «merge».

Плюс второго способа в том, что можно вставить какое-то дефолтное значение, если наше условие сцепки не сработало. Кстати, это все работает и в Oracle 10g, в 9 версии тоже.

Надеюсь, было все понятно и кому-то пригодится это.

Удачи!

Одна мысль про “Сложный Update с использованием нескольких таблиц в Oracle 11 R2

  1. В первом примере ты сделал алиасы:
    s.adress_shop AS adress_shop_new,
    s.adress_client AS adress_client_new

    а во втором указал готовыми алиасами.
    UPDATE SET t.adress_shop=p.adress_shop_new, t.adress_client=p.adress_client_new

    это правильно?

    • Да, всё правильно! В первом случае алиасы нужны, чтобы задать точное определение столбцам из разных таблиц для корректного апдейта, чтобы Oracle понимал, откуда брать данные и где апдейтить. Во втором случае, весь запрос оборачивается в алиас (SELECT * FROM table2 s) p, тоже, чтобы понимать, откуда что брать и где апдейтить. Весь код я проверял на бою =)

      • Но ты прав, лишняя приписка «_new». Должно быть так в merge — t.adress_shop=p.adress_shop, t.adress_client=p.adress_client.
        Спасибо за ошибку, поправлю 😉

  2. Добавлю еще замечание по поводу подзапросов. Мне требовалось обновить довольно большой объем данных и «на попробовать», чтобы не класть всю базу сразу, я решил ограничить количество строк в подзапросе. Апдейт не заработал с ошибкой «data manipulation operation not legal on this view».
    Ответ нашел здесь: http://sql.az/index.php?option=com_content&view=article&id=79&lang=ru
    Подробно описано, почему нельзя использовать ROWNUM, и какие еще есть ограничения на позапросы для апдейта.

  3. А как на oracle обновить поле в таблице, заполнив его номером текущей строки? Спасибо

  4. Спасибо большое, код рабочий! Пробовал вариант с MERGE (после JOIN с которым что-то запутался, может просто спешил, тут просто по работе нужно было все строчно и сию секунду) еще раз спасибо!

  5. У меня вот такой вопрос. Есть таблица INFO (СREATE TABLE info (num, last_name, first_name, sndoc, sdoc,numdoc))
    Суть в следующем. В поле sndoc данные все вида SSnnnnnnn, где S — любое число, а N — любая цифра, а поля sdoc, numdoc — пустые.
    Нужно выполнить UPDATE который из sndoc в sdoc, numdoc соответственно вставит символы и цифры. SUBSTR (sndoc, 1, 2) — этим select’ом получаю символы а SUBSTR (sndoc, 3, 9) получаю цифры. Как выполнить UPDATE c этими SELECT’ами?

  6. Подскажите, пожалуйста, как можно наложить ограничение на поля в табл t, когда нужен update не по всем полям, а, например, где t.adress_client начинается на А?

    • Просто необходимо указать условие «where», т.е. будет что-то подобно этому «where t.adress_client like ‘А%'». Если будет что-то не ясно, пишите, разберемся 🙂

    • Нет, нельзя, Oracle даже выдает ошибку на это «ORA-01776: cannot modify more than one base table through a join view». Можно написать специальную процедуру, где можно это попробовать реализовать, либо просто написать несколько апдейтов для нескольких таблиц, если уж не хочется совсем замарачиваться с процедурой=)

  7. Можете полностью написать скрипт по обновлению, а то у меня без INSERT’а не получается

    • Немного не понял вопроса. Скрипт я написал полностью рабочий вариант, только название таблиц и столбцов свои. Какая задача стоит у вас? А я попробую помочь 🙂

      • вот так скрипт написал:
        MERGE INTO tmp_1 t
        USING (SELECT * FROM tmp) p
        ON (p.scm_id=t.sc_id)
        WHEN MATCHED THEN—когда совпали договора, то идет сам update
        UPDATE SET t.smart_script_id=p.smart_script_id
        WHEN NOT MATCHED THEN UPDATE SET t.smart_script_id=’Nichego’;

        но выдает ошибку:
        ORA-00905: missing keyword

        • Да, он правильно выдает ошибку. После «WHEN NOT MATCHED THEN» можно писать или insert, или delete. Update он не воспринимает. Я исправил в статье. Когда проверял, по другому у себя в Oracle писал, а тут по другому вставил. Спасибо, что указал на ошибку 🙂 Сегодня в Oracle 11g все проверил — этот вариант работает. Только когда будешь вставлять значение в одно поле, смотри, другое поле в этой строке будет null содержать.

  8. Извини, но дело не в том что комент был не там поставлен.
    Правильный синтаксис такой:
    MERGE INTO vit.temp_mt p
    USING (select * from vit.mt) t ON (p.id=t.id)
    WHEN MATCHED THEN
    UPDATE SET p.fam=t.fam
    WHEN NOT MATCHED THEN insert (p.fam)
    values(‘Пупкин’)
    Проверено. Иначе выдает «missing keyword», наверное имеется ввиду, что пропущено ключевое слово insert.
    Но все равно спасибо за урок.

    • Может кому интересно.
      В SQL server синтаксис сложного апдейта будет таким:
      update t1 set fam=t2.fam
      from t1 d join t2 on d.id=t2.id

    • Нет, ты не понял, я в коде в редактировании статьи пропустил слово =) Скрипт, который ты написал, полностью соответствует моему выше описанному 😉 Только название таблиц и полей другое.

    • Нет, здесь insert и не нужен, в моем примере, в Oracle 11g все работает как часы =) Когда идет обновление в sql, insert можно не использовать, здесь все зависит от поставленной задачи. В моем случае просто одно значение заменяется на другое. Ты допилил скрипт по своему, поэтому ошибки нет, я же использовал ключевое слово update и все. =) Т.е. мой скрипт корректно выполняется в базе Oracle. Проверено не один раз.

  9. Второй вариант выдает ошибку ORA 02012 и как с этим бороться пока не нашел. Если знаешь как решить проблему, подскажи, пожалуйста.
    Использую TOAD for Oracle

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

*