СУБД ЛИНТЕР — Документация

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

Функция

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

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

<запрос корректировки>::=

UPDATE [имя схемы.]имя объекта
[[AS] псевдоним объекта]
[JOIN соединяемый объект
[[AS] псевдоним соединяемого объекта[, …]]
SET значение корректировки[, …]
[WHERE-спецификация]
[ WAIT | NOWAIT ]
[{QUANT | QUANTUM} TIMEOUT время]
[WITH PRIORITY приоритет]

<имя объекта>::=

имя таблицы[метка доступа]|имя представления

<псевдоним объекта>::=

идентификатор

<соединяемый объект>::=

имя таблицы|имя представления

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

идентификатор

<значение корректировки>::=

имя столбца [метка доступа] =
{ значимое выражение
| подзапрос
| DEFAULT
| NULL
| EXTFILE(NULL | ? | спецификация файла[, имя фильтра])}

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

<Имя таблицы> должно задавать пользовательскую базовую таблицу или пользовательское обновляемое представление.
На таблицу с <именем таблицы> после фразы 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 |
```
Имя столбца в <значении корректировки> должно принадлежать только <имени таблицы> из фразы UPDATE (не JOIN).
```
UPDATE Auto JOIN Person SET auto.make='Ford' WHERE auto.personid=person.personid and auto.year>70; 
```

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

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';
…

Числовое значение <значимого выражения> допускается представлять в виде строкового значения без использования оператора преобразования типа данных.

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|

В качестве <значимого выражения> можно использовать <логическое выражение>.

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|

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

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

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;

Если <подзапрос> вернул пустую выборку, изменяемому значению присваивается 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|        |

Опция DEFAULT присваивает столбцу установленное для него значение по умолчанию.
Если задана конструкция 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 можно изменять напрямую или через обновляемое представление записи только в одной таблице или выборке из неё без соединения с другими таблицами.
Для корректировки записей в <имени объекта> рекомендуется, чтобы каждой записи из <имени объекта> соответствовала только одна запись первой и последующих соединяемых таблиц. В противном случае результат выполнения запроса не предсказуем.

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

выполняется соединение таблиц;
обновляются записи результирующего набора, которые удовлетворяют <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 этаже.

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

Областью видимости таблицы <имя таблицы> является весь <запрос корректировки>. При этом:
- при отсутствии <спецификатора выборки> будут изменены все записи таблицы;
- если задана <WHERE-спецификация>, то изменению подвергается каждая запись таблицы с <именем таблицы>, для которой результат <WHERE-спецификации> истинный. Каждый <подзапрос> в <WHERE-спецификации> реально выполняется для каждой записи в таблице, а результаты <подзапроса> используются в <WHERE-спецификации> применительно к данной записи.
Корректировка записей выполняется следующим образом:
- создается строка-кандидат, структура данных которой совпадает со структурой строки базовой таблицы. Если <имя таблицы> идентифицирует представление, то в качестве базовой таблицы выступает та, из которой создано данное представление;
- для каждого <значения корректировки> значение заданного столбца в строке-кандидате заменяется заданным корректируемым значением;
- корректируемая строка заменяется строкой-кандидатом.
<Значение корректировки> не должно включать столбцы типа AUTOROWID, AUTOINC и псевдостолбцы ROWID, ROWTIME, DBROWTIME, BLOB-столбцы.
При присвоении значения CAST AS <строковый тип> некоторому столбцу без явного указания длины строкового типа длина делается равной длине столбца.
При обновлении записей в таблице, содержащей столбец с модификатором AUTOROWID, обновление значений в этом столбце запрещено.
Команда UPDATE CURRENT OF … выполняется по тому каналу, по которому она была подана (а не по каналу, по которому был подан соответствующий SELECT-запрос).
Параметр <время> задает максимально допустимую продолжительность выполнения запроса (от 1 до 65535 сек.). Если запрос в отведенное для него время не был выполнен, его обработка прекращается с выдачей соответствующего кода завершения.
Конструкция WITH PRIORITY <приоритет> устанавливает заданный приоритет (значение в диапазоне от 0 до 255) выполняемому запросу. Если задать приоритет больше текущего приоритета пользователя, от имени которого подается запрос, то выдается код завершения 1022 («Нарушение привилегий»).
Конструкция WITH PRIORITY <приоритет> назначает приоритет только тому запросу, в котором она указана. На приоритет любых последующих запросов она не влияет.
Для таблицы, созданной с атрибутом NODE, <запрос корректировки> с локального сервера недоступен.

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

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|

Модификация значений идентификационных столбцов разрешена только для столбцов, созданных с атрибутом GENERATED BY DEFAULT.
Для таблиц «в памяти» <запрос корректировки> в режиме OPTIMISTIC не поддерживается.
Разрешены внешние ссылки в SELECT-списке. Кроме того, при проверке наличия столбцов под агрегатными функциями и без них эти внешние ссылки не учитываются (т.е. рассматриваются как константы).
```
Пример запроса с использованием такой ссылки (к таблицам S, SP в дистрибутивной базе):
update s set status = (select s.status+count(*) from sp where s.snum = sp.snum);
```

Пример

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

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

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