Корректировка записи
Функция
Определение запроса корректировки записи таблицы.
Спецификация
::=
[[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);
-
Любой JOIN (больше одной таблицы во FROM), либо обращение к VIEW на основе JOIN, либо при работе в режиме /COMPATIBILITY=STANDARD, либо VIEW было создано при работе в таком режиме автоматически является необновляемым (будет выдан код завершения 2162).
Пример
Непорционная загрузка BLOB-данных (например, сразу 1 Мбайт). В этом случае необходимо использовать параметрический запрос:
update T_BLOB set BLB = ? WHERE ...; или insert into T_BLOB (..., BLB) values (..., ?);
привязав к параметру для BLOB-значения соответствующий массив.