Корректировка записи

Функция

Определение запроса корректировки записи таблицы.

Спецификация

   
< псевдоним объекта >::=
< соединяемый объект >::=
< псевдоним соединяемого объекта >::=
< значение корректировки >::=

Синтаксические правила

  1. < Имя таблицы > должно задавать пользовательскую базовую таблицу или пользовательское обновляемое представление.

  2. На таблицу с < именем таблицы > после фразы UPDATE должны быть привилегии UPDATE, для остальных таблиц (из списка JOIN) – привилегии SELECT.

    create table tab1 (type char(10), id int);
    insert into tab1 (type,id) values('System', 1);
    UPDATE tab1 SET type=NULL,id=id+3 WHERE id=1;
    select * from tab1;
    |NULL |4 |
  3. Имя столбца в < значении корректировки > должно принадлежать только < имени таблицы > из фразы UPDATE (не JOIN).

    UPDATE Auto JOIN Person SET auto.make='Ford' 
     WHERE auto.personid = person.personid and auto.year >70; 
  4. Тип < значимого выражения > должен позволять автоматическое преобразование к типу столбца.

    create table tab1 (num decimal);
    update tab1 set num=length(user) where rowid=12;
    update tab1 join tab2
       set tab1."Сумма"=tab2."Должн. коэфф."*tab1."Оклад"
     where tab1."Ид. Должности"=tab2."Ид. Должности";
    
    Добавление строковых порций данных в конец BLOB-столбца BLB таблицы T_BLOB:
    update T_BLOB set  BLB=BLB+'порция 1';
    update T_BLOB set  BLB=BLB+'порция 2';
    …
  5. Числовое значение < значимого выражения > допускается представлять в виде строкового значения без использования оператора преобразования типа данных.

    create or replace table tst (i1 int, i2 int, i3 int, db1 double, db2 double);
    insert into tst values (15, '15', cast '15' as int, 103.545, '1.03545e+2');
    update tst set i1=20, i2='20', i3=cast '20' as int;
    select * from tst;
    |         15|         15|         15|            103.545|           103.545|
    |         20|         20|         20|            103.545|           103.545|
  6. В качестве < значимого выражения > можно использовать < логическое выражение >.

    create or replace table tst (i int, b boolean);
    insert into tst values (1,TRUE);
    insert into tst values (2,FALSE);
    update tst set b=100-2 >97 where i=2;
    select * from tst;
     I           B
     -           -
    |          1|T|
    |          2|T|
  7. В качестве < значимого выражения > можно использовать неименованный (?) или именованный (:имя) параметр. В этом случае тип параметра предполагается совпадающим с типом столбца, которому присваивается < значимое выражение >, если только тип параметра не указан явно.

  8. < Подзапрос > должен возвращать единственное значение.

    create table tab1 (i int autoinc, c char(40));
    insert into tab1 default values;
    insert into tab1 default values;
    insert into tab1 default values;
    update tab1 join auto
       set c= (select 'Модель ' ||model || ' (' || to_char(year+1900, '9999') || ')'
      from auto where tab1.i=auto. personid);
    
    select * from tab1;
    | 1 | Модель MERCURY COMET GT V8 (1971) |
    | 2 | Модель A-310 (1970)               |
    | 3 | Модель MATADOR STATION (1971)     |
    
    update tab1 join auto set c= (select to_char(sysdate, 'dd.mm.yyyy') )
     where tab1.i=auto. personid;
  9. Если < подзапрос > вернул пустую выборку, изменяемому значению присваивается NULL-значение.

    select rowid, i from tst;
    |          1|        100|
    update tst set i=(select rowid from auto where make='АвтоВАЗ') where rowid=1;
    select rowid, i from tst;
    |          1|        |
  10. Опция DEFAULT присваивает столбцу установленное для него значение по умолчанию.

  11. Если задана конструкция JOIN, то она должна содержать список реальных или порожденных таблиц, соединяемых с таблицей, в которой выполняется корректировка записей. Условие соединения всех таблиц задается в < WHERE-спецификации >.

    update auto JOIN person set auto.year=2014 WHERE  auto.personid=person.personid
       and auto.year=70;
    
    update tab1 as a join (select user_id as id, sum(ptz) as s
                             from tab2
                            where PR_ID<  >0 and PR_ID<  >-1
                            group by user_id) as b
       set a.ptz_m=s
     where a.user_id=b.id;

    Примечание

    Синтаксис оператора UPDATE с соединяемыми таблицами не соответствует стандарту SQL, т.к. в этом стандарте не прописан механизм корректировки записей при наличии соединяемых таблиц. Согласно стандарту SQL можно изменять напрямую или через обновляемое представление записи только в одной таблице или выборке из неё без соединения с другими таблицами.

  12. Для корректировки записей в < имени объекта > рекомендуется, чтобы каждой записи из < имени объекта > соответствовала только одна запись первой и последующих соединяемых таблиц. В противном случае результат выполнения запроса не предсказуем.

  13. Корректировка записей с использованием соединения таблиц происходит следующим образом:

    • выполняется соединение таблиц;

    • обновляются записи результирующего набора, которые удовлетворяют < WHERE-спецификации >.

    Тестовые данные:
    
    Пусть имеются таблицы сотрудников (persons), отделов (departments), этажей (floors).
    Таблица persons связана с departments по номеру отдела (d_id), departments связана с floors по номеру этажа (num_f).
    
    create or replace table floors (num_f int, f_name char(20));
    insert into floors values (1, 'First');
    insert into floors values (2, 'Second');
    insert into floors values (3, 'Third');
    insert into floors values (4, 'Fourth');
    insert into floors values (5, 'Fifth');
    insert into floors values (6, 'Sixth');
    
    create or replace table departments(d_id int, d_name char(20), num_f int);
    insert into departments values (1, 'Sales',           1);
    insert into departments values (2, 'IT-technologies', 3);
    insert into departments values (3, 'Finance',         4);
    insert into departments values (4, 'Management',      4);
    insert into departments values (5, 'Design',          3);
    
    create or replace table persons(p_id int, p_name char (20), d_id int);
    insert into persons values (1, 'John',  3);
    insert into persons values (2, 'Mary',  2);
    insert into persons values (3, 'Kate',  4);
    insert into persons values (4, 'Jack',  2);
    insert into persons values (5, 'Peter', 7);
    insert into persons values (6, 'Ann',   5);
    Обновить список сотрудников, заменив номер отдела на 5-ый у тех сотрудников, которые работают на 4 этаже (в запросе обновится только левая таблица, т.е. persons):
    
    UPDATE persons p JOIN departments d 
       SET p.d_id = 5 
     WHERE (p.d_id = d.d_id) 
       AND (d.num_f = 4);
    
    SELECT * FROM persons;
    
     P_ID        P_NAME               D_ID
     ----        ------               ----
    |          1|John                |          5|
    |          2|Mary                |          2|
    |          3|Kate                |          5|
    |          4|Jack                |          2|
    |          5|Peter               |          7|
    |          6|Ann                 |          5|
    
    В результирующем наборе у сотрудников 'John' и 'Kate' номера отделов поменялись на '5', т.к. эти сотрудники работали соответственно в отделах 'Finance' и 'Management', которые находятся на 4 этаже.

Общие правила

  1. Областью видимости таблицы < имя таблицы > является весь < запрос корректировки >. При этом:

    • при отсутствии < спецификатора выборки > будут изменены все записи таблицы;

    • если задана < WHERE-спецификация >, то изменению подвергается каждая запись таблицы с < именем таблицы >, для которой результат < WHERE-спецификации > истинный. Каждый < подзапрос > в < WHERE-спецификации > реально выполняется для каждой записи в таблице, а результаты < подзапроса > используются в < WHERE-спецификации > применительно к данной записи.

  2. Корректировка записей выполняется следующим образом:

    • создается строка-кандидат, структура данных которой совпадает со структурой строки базовой таблицы. Если < имя таблицы > идентифицирует представление, то в качестве базовой таблицы выступает та, из которой создано данное представление;

    • для каждого < значения корректировки > значение заданного столбца в строке-кандидате заменяется заданным корректируемым значением;

    • корректируемая строка заменяется строкой-кандидатом.

  3. < Значение корректировки > не должно включать столбцы типа AUTOROWID, AUTOINC и псевдостолбцы ROWID, ROWTIME, DBROWTIME, BLOB-столбцы.

  4. При присвоении значения CAST AS < строковый тип > некоторому столбцу без явного указания длины строкового типа длина делается равной длине столбца.

  5. При обновлении записей в таблице, содержащей столбец с модификатором AUTOROWID, обновление значений в этом столбце запрещено.

  6. Команда UPDATE CURRENT OF … выполняется по тому каналу, по которому она была подана (а не по каналу, по которому был подан соответствующий SELECT-запрос).

  7. Параметр < время > задает максимально допустимую продолжительность выполнения запроса (от 1 до 65535 сек.). Если запрос в отведенное для него время не был выполнен, его обработка прекращается с выдачей соответствующего кода завершения.

  8. Конструкция WITH PRIORITY < приоритет > устанавливает заданный приоритет (значение в диапазоне от 0 до 255) выполняемому запросу. Если задать приоритет больше текущего приоритета пользователя, от имени которого подается запрос, то выдается код завершения 1022 («Нарушение привилегий»).

  9. Конструкция WITH PRIORITY < приоритет > назначает приоритет только тому запросу, в котором она указана. На приоритет любых последующих запросов она не влияет.

  10. Для таблицы, созданной с атрибутом NODE, < запрос корректировки > с локального сервера недоступен.

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

    create or replace table tst
    ("Сумма" numeric, "Скидка" numeric,  "Итого"
    generated always as (("Сумма"*(100-"Скидка")/100)));
    insert into tst ("Сумма", "Скидка", "Итого" ) values (1000,5,default);
    insert into tst ("Сумма", "Скидка", "Итого" ) values (2000,7,default);
    select * from tst;
    |  1000.0|  5.0|   950.0|
    |  2000.0|  7.0|  1860.0|
    update tst set "Скидка"="Скидка"+0.5;
    select * from tst;
    |  1000.0|  5.5|   945.0|
    |  2000.0|  7.5|  1850.0|
  12. Модификация значений идентификационных столбцов разрешена только для столбцов, созданных с атрибутом GENERATED BY DEFAULT.

  13. Для таблиц «в памяти» < запрос корректировки > в режиме OPTIMISTIC не поддерживается.

  14. Разрешены внешние ссылки в SELECT-списке. Кроме того, при проверке наличия столбцов под агрегатными функциями и без них эти внешние ссылки не учитываются (т.е. рассматриваются как константы).

    Пример запроса с использованием такой ссылки (к таблицам S, SP в дистрибутивной базе):
    update s set status = (select s.status+count(*) from sp where s.snum = sp.snum);
  15. Любой JOIN (больше одной таблицы во FROM), либо обращение к VIEW на основе JOIN, либо при работе в режиме /COMPATIBILITY=STANDARD, либо VIEW было создано при работе в таком режиме автоматически является необновляемым (будет выдан код завершения 2162).

Пример

Непорционная загрузка BLOB-данных (например, сразу 1 Мбайт). В этом случае необходимо использовать параметрический запрос:

update T_BLOB set  BLB = ? WHERE ...;
  или
insert into T_BLOB (..., BLB) values (..., ?);

привязав к параметру для BLOB-значения соответствующий массив.