Изменение соединения

Источник: ln

Эта статья посвящена "хитрым" операторам UPDATE, изменяющим данные в одной таблице на основе данных из другой. Попутно обсуждаются причины возникновения ошибки ORA-01779 при выполнении таких действий путем изменения соединения. По мотивам очередного ответа Тома Кайта.

Оператор UPDATE и NULL-значения

Том,

При изменении столбца с помощью оператора update, значение некоторых записей (которые не надо менять) изменяется на NULL. Я использую следующий оператор:

update    table    name B
  set        columnname    = 
    (    select    value
          from     lookup    O
          where    B.keyname = O.keyname
            and    O.Othercolumn = Other_value);

В результате выполняются все необходимые изменения, но изменяются и записи, которые не надо менять: они получают значения Null. Можно ли этого избежать, поскольку нам надо часто изменять записи, но не все сразу.

Нет ли способа изменить только те записи, которые нужно, и не сбросить значения в других записях в Null?

Ответ Тома Кайта

Есть как минимум 2 способа правильно выполнить такого рода коррелированное изменение. Я покажу свой любимый метод (изменение соединения) и другой метод, который будет работать если нельзя задать ограничение уникальности по LOOKUP(keyname) (что необходимо для успешного изменения соединения).

Рассмотрим следующие тестовые таблицы:

scott@ORA734.WORLD> create table name
  2  ( keyname int,
  3    columnName varchar2(25)
  4  )
  5  /
Table created.

scott@ORA734.WORLD> create table lookup
  2  ( keyname int PRIMARY KEY,
  3    value varchar2(25),
  4    otherColumn int
  5  )
  6  /
Table created.

scott@ORA734.WORLD> insert into name values ( 100, 'Original Data' );
1 row created.

scott@ORA734.WORLD> insert into name values ( 200, 'Original Data' );
1 row created.

scott@ORA734.WORLD> insert into lookup values ( 100, 'New Data', 1 );
1 row created.

scott@ORA734.WORLD> commit;
Commit complete.

А вот параметр "other_value", который вы используете в своем операторе update...

scott@ORA734.WORLD> variable other_value number
scott@ORA734.WORLD> exec :other_value := 1
PL/SQL procedure successfully completed.

scott@ORA734.WORLD> select * from name;

   KEYNAME COLUMNNAME
---------- -------------------------
       100 Original Data
       200 Original Data

Далее мы изменяем соединение. Можно изменять значение столбцов только в одной из таблиц, а другие таблицы, которые мы не изменяем, должны быть "защищены ключом" - мы должны иметь гарантию, что при соединении NAME со второй таблицей будет возвращено не более одной записи. Для этого столбец keyname в таблице LOOKUP должен быть либо первичным ключом, либо быть связанным ограничением уникальности...

scott@ORA734.WORLD> update
  2    ( select columnName, value
  3        from name, lookup
  4       where name.keyname = lookup.keyname
  5         and lookup.otherColumn = :other_value )
  6     set columnName = value
  7  /

1 row updated.

scott@ORA734.WORLD> select * from name;

   KEYNAME COLUMNNAME
---------- -------------------------
       100 New Data
       200 Original Data

Обратите внимание, изменилась только интересующая нас строка - остальные данные не затронуты...

scott@ORA734.WORLD> rollback;
Rollback complete.

scott@ORA734.WORLD> select * from name;

   KEYNAME COLUMNNAME
---------- -------------------------
       100 Original Data
       200 Original Data

Следующий способ сработает безо всяких ограничений - не нужно первичного ключа млм ограничения уникальности для таблицы lookup (но лучше точно знать, что подзапрос вернет 0 или 1 запись!).

Оператор очень похож на ваш update, но имеет конструкцию where, чтобы изменялись только строки, для которых есть соответствия...

scott@ORA734.WORLD> update name
  2     set columnName = ( select value
  3                          from lookup
  4                         where lookup.keyname = name.keyname
  5                           and otherColumn = :other_value )
  6   where exists ( select value
  7                    from lookup
  8                   where lookup.keyname = name.keyname
  9                     and otherColumn = :other_value )
 10  /

1 row updated.

scott@ORA734.WORLD> select * from name;

   KEYNAME COLUMNNAME
---------- -------------------------
       100 New Data
       200 Original Data

А можно ли добиться того же для нескольких столбцов?

Том,

Мне не удалось сделать следующее. Я создал две таблицы t и t1 с помощью select * from user_objects. В таблицах t и t1 - по 117 записей. Затем я взял один object_name(PRIMARY_SALESFORE) в таблице t и изменил три записи - object_type, object_id и created.

ADHOC@VOYAGER> select t.object_id,t1.object_id,t.object_type,t1.object_type,
  2  t.created,t1.created
  3  from t,t1
  4  where t1.object_name=t.object_name
  5  and t.object_name='PRIMARY_SALESFORCE';

OBJECT_ID OBJECT_ID OBJECT_TYPE   OBJECT_TYPE   CREATED   CREATED
--------- --------- ------------- ------------- --------- ---------
     2222     17927 ORATAB        TABLE         02-APR-02 01-APR-02

Теперь при попытке изменить эти три поля в таблице t на основе t1 я получаю следующее сообщение об ошибке. Что я делаю не так?

1  update
2  (select t.object_id,t1.object_id,t.object_type,t1.object_type,
3  t.created,t1.created
4  from t,t1
5  where t1.object_name=t.object_name
6  and t.object_name='PRIMARY_SALESFORCE')
7  set t.object_id=t1.object_id,
8  t.object_type=t1.object_type,
9* t.created=t1.created
ADHOC@VOYAGER> /
update
*
ERROR at line 1:
ORA-00904: invalid column name

При попытке изменить всего один столбец в T я тоже получаю сообщение об ошибке:

ADHOC@VOYAGER> update 
  2  (select t.object_id toid,t1.object_id t1oid
  3  from t,t1
  4  where t.object_name=t1.object_name
  5  and upper(t.object_name)='PRIMARY_SALESFORCE')
  6  set toid=t1oid;             
update
*
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table 

Ответ Тома Кайта

Таблицы T и T1 "невидимы" за пределами подставляемого представления (inline view). Вы это исправили, добавив псевдонимы toid, t1oid.

Что касается второй ошибки, пожалуйста, перечитайте представленный выше ответ. НАДО, чтобы столбец object_id таблицы T1 был связан ограничением первичного ключа/уникальности, чтобы каждая строка таблицы T соединялась НЕ БОЛЕЕ, чем с одной строкой таблицы T1.

Пусть в таблице T имеется:

OBJECT_NAME          OBJECT_ID
--------------       ----------------
ABC                  xxxx

А в таблице T1:

OBJECT_NAME          OBJECT_ID
----------------     ------------------
ABC                  123
ABC                  456

что должно быть в результате:

ADHOC@VOYAGER> update 
  2  (select t.object_id toid,t1.object_id t1oid
  3  from t,t1
  4  where t.object_name=t1.object_name
  5  and upper(t.object_name)='PRIMARY_SALESFORCE')
  6  set toid=t1oid;  

надо ли при соединении брать 123 или 456, и в каких случаях? Поскольку результат этого изменения на 100% НЕОДНОЗНАЧНЫ, мы его не допускаем. Необходимо гарантировать защиту ключом с помощью ограничения первичного ключа/уникальности.

Соответствующий индекс в любом случае нужен из соображений производительности.

Комментарий читателя от 2 апреля 2002 года

Спасибо, Том. Я знал, что если еще раз (внимательно) перечитаю твой ответ, то смогу найти ошибку в SQL-операторе. Но, в любом случае, не мог бы ты помочь мне добиться желанной цели - изменить три столбца из другой таблицы, имеющий один совпадающий столбец (object_name), уникальный, но не являющийся первичным ключом.

Ответ Тома Кайта

update t
  set ( f1, f2, f3 ) = 
        ( select a, b, c from t2 where t2.some_field = t.some_field )
 where exists ( select a, b, c from t2 where t2.some_field = t.some_field )
/

Этот запрос позволит решить вашу проблему. Если нельзя задать ограничение уникальности (которое нужно ТЕОРЕТИЧЕСКИ, даже если не потребуется практически - если значения в столбце some_field таблицы t2 окажутся НЕ уникальными, представленный запрос закончится сообщением об ошибке "подзапрос вернул более одной строки", потому что оператор update будет НЕОДНОЗНАЧНЫМ)

Хороший прием. Комментарий читателя от 9 апреля 2002 года

Спасибо за прекрасный сайт. Я пытаюсь изменить таблицу на основе определенной информации в той же таблице. Запрос выглядит следующим образом:

DECLARE
CURSOR c1 IS SELECT ROWID FROM
siebel.s_synd_data
    where
        WHERE
     ROW_ID LIKE 'DDM%'
     AND DATA_SRC_CD = 'DECPRF'
     and POSITION_ID IS NULL;
begin
for cur in c1 loop
     UPDATE SIEBEL.S_SYND_DATA A
     SET A.POSITION_ID = (SELECT B.ROW_ID FROM SIEBEL.S_POSTN B, 
SIEBEL.S_ASGN_GRP C
WHERE A.TERR_ID = C.ROW_ID
AND SUBSTR(C.NAME, 1, 5) // '00D' = B.NAME)
   where rowid=cur.rowid;
  if mod(c1%rowcount,50000)=0 then
           commit;
  end if;
end loop;
commit;
end ;

Предполагается, что условию WHERE курсора C1 будет удовлетворять 9 миллионов строк. Мой АБД говорит, что транзакция будет зафиксирована только после изменения 9 миллионов строк, и мне надо фиксировать ее после каждых 50000 - правильно ли это?

Что можно сделать, чтобы это изменение выполнялось быстрее - я знаю, что ты ненавидишь такие вопросы, если за ними не стоят определенные критерии, но что, если я создам индекс (по функции)?

Ответ Тома Кайта

Я лично делал бы так:

update s_synd_data a   -- извините, я не могу заставить себя набрать sie....
   set a.position_id = ( select b.row_id 
                           from s_postn b, s_asng_grp c
                          where c.row_id = a.terr_id
                            and b.name = substr(c.name,1,5) // '00D' )
 where row_id like 'DDM%'
   and data_src_cd = 'DECPRF'
   and position_id is null;

Если бы по столбцу position_id был индекс, я бы его удалил, выполнил изменение и пересоздал индекс без возможности восстановления и с распараллеливанием.

Прежде чем вы скажете: "но там же 9 миллионов строк - надо фиксировать по частям", я отвечу - ни в коем случае. Проверьте только, что выделено достаточно сегментов отката (иначе, при использовании вашего подхода, РАНО ИЛИ ПОЗДНО, вы получите сообщение об ошибке ORA-01555 - поищите на сайте обсуждения по слову 01555 или 1555 и поймете, что я имею ввиду).

Потребуется не так уж много места в сегментах отката, как может показаться. Процедурный код, вроде вашего:

  • будет работать медленне простого оператора update
  • сгенерирует больше данных отмены и повторного выполнения, чем один оператор update
  • будет источником ошибок (ora-01555, другие логические ошибки)
  • у вас уже есть логическая ошибка - c1%rowcount не содержит "суммарное" значение; это колчиество строк, измененных последним оператором update. Если вы измените фактически менее 50000 строк одним оператором, транзакция не будет зафиксирована (в вашем коде - ошибка)
  • вообще - плохая идея.

В своей системе я только что изменил 1099008 строк:

ops$tkyte@ORA817DEV.US.ORACLE.COM> update big_table set owner = lower(owner);
Elapsed: 00:08:38.66
ops$tkyte@ORA817DEV.US.ORACLE.COM> select used_ublk from v$transaction;

 USED_UBLK
----------
     13002
Elapsed: 00:00:00.01
ops$tkyte@ORA817DEV.US.ORACLE.COM> select 13002*8/1024 from dual;

13002*8/1024
------------
  101.578125
Elapsed: 00:00:00.01
ops$tkyte@ORA817DEV.US.ORACLE.COM> select count(*) from big_table;

  COUNT(*)
----------
   1099008
Elapsed: 00:00:02.41

для этого потребовалось около 100 Мбайт в сегменте отката. Вам следует ожидать порядка гигабайта. Я бы просто убедился (с помощью АБД), что столько места есть, использовал бы оператор set transaction use rollback segment при необходимости (можно просто создать временный сегмент для этого оператора update в каталоге /tmp, например, и потом его удалить). Выполните update. Этого достаточно.

Меньше думать, меньше делать, меньше вероятность ошибки...

Комментарий читателя от 30 апреля 2002 года

Уважаемый Том...

В какой версии Oracle появилась возможность выполнять "изменение соединения"? Я всегда использовал для таких запросов второй вариант, с конструкцией EXISTS. Всегда казалось смешным, что приходится один и тот же подзапрос вводить дважды.

Тебе надо написать книжку о хитрых приемах в Oracle SQL.

Ответ Тома Кайта

Уверен, что эта возможность появилась в версии 7.3

ops$tkyte@ORA734.WORLD> create table t1 ( x int primary key, y int );

Table created.

ops$tkyte@ORA734.WORLD> create table t2 ( x int, y int );

Table created.

ops$tkyte@ORA734.WORLD> insert into t1 values ( 1, 1 );

1 row created.

ops$tkyte@ORA734.WORLD> insert into t1 values ( 2, 2 );

1 row created.

ops$tkyte@ORA734.WORLD> insert into t2 values ( 2, null );

1 row created.

ops$tkyte@ORA734.WORLD> insert into t2 values ( 3, null );

1 row created.

ops$tkyte@ORA734.WORLD> update ( select t1.y t1_y, t2.y t2_y
  2                     from t1, t2
  3                    where t1.x = t2.x )
  4     set t2_y = t1_y
  5  /

1 row updated.

ops$tkyte@ORA734.WORLD> select * from t2;

         X          Y
---------- ----------
         2          2
         3

Параллельные операторы ЯМД и изменение представления с соединением

ADHOC@VOYAGER> update 
  2  (select t.object_id toid,t1.object_id t1oid
  3  from t,t1
  4  where t.object_name=t1.object_name
  5  and upper(t.object_name)='PRIMARY_SALESFORCE')
  6  set toid=t1oid;  

В представленном выше запросе, который демонстрирует изменение представления с соединением, меня интересует, выполняется ли это изменение параллельно, если таблица t фрагментирована и выполнен оператор alter session enable parallel dml. Я использую аналогичный оператор update и выяснил, что лучшим планом выполнения оператора select будет полный просмотр обеих таблиц и соедитнение хешированием.

Ответ Тома Кайта

Если удовлетворены все остальные критерии (включено распараллеливание, таблицы допускают "распараллеливание") - должно выполняться параллельно.

Изменение будет выполняться по фрагментам таблицы T, с полным просмотром каждого фрагмента T и соединением его с таблицей T1 после фильтрации по условию upper(t.object_name).

Один из способов убедиться в этом - выполнить изменение, а затем - оператор select * from t where rownum = 1; Если этот оператор сработает, изменение не распараллеливалось, а если нет - было выполнено параллельно. Выполните Commit и сможете выбирать данные.

Можно ли это сделать, если задействовано более двух таблиц?

Я делал это, когда в соединении задействовано две таблицы.

Можно ли это сделать, если необходимо изменить столбец таблицы A в соответствии со значениями столбца в таблице C. Таблицы связаны так: A -> B -> C, где A является главной для всех, а B - главной для C?

Ответ Тома Кайта

Ай-я-яй, такой простой пример - вы же все уже указали - и не попытаться сделать... Ладно.

Ответ - да, если соблюдается "защита ключом", другими словами, каждая строка таблицы A появится в результате соединения НЕ БОЛЕЕ ОДНОГО РАЗА. В противном случае - нет.

Рассмотрим простой пример:

ops$tkyte@ORA920.LOCALHOSTgt; create table a ( x int primary key, y int );

Table created.

ops$tkyte@ORA920.LOCALHOST> create table b ( x references a primary key );

Table created.

ops$tkyte@ORA920.LOCALHOST> create table c ( x references b primary key, y int );

Table created.

ops$tkyte@ORA920.LOCALHOST> insert into a values ( 1, null );

1 row created.

ops$tkyte@ORA920.LOCALHOST> insert into a values ( 2, null );

1 row created.

ops$tkyte@ORA920.LOCALHOST> insert into b values ( 1 );

1 row created.

ops$tkyte@ORA920.LOCALHOST> insert into c values ( 1, 100 );

1 row created.

ops$tkyte@ORA920.LOCALHOST> select * from a;

         X          Y
---------- ----------
         1
         2

ops$tkyte@ORA920.LOCALHOST> update ( select a.y a_y, c.y c_y
  2          from a, b, c
  3             where a.x = b.x and b.x = c.x )
  4      set a_y = c_y;

1 row updated.

ops$tkyte@ORA920.LOCALHOST> select * from a;

         X          Y
---------- ----------
         1        100
         2

Блокировки. Комментарий читателя от 2 октября 2002 года

Том,

В случае:

update
      ( select columnName, value
          from name, lookup
         where name.keyname = lookup.keyname
           and lookup.otherColumn = :other_value )
       set columnName = value
    /

будут ли обе таблицы, name и lookup, заблокированы по ходу изменения?

Ответ Тома Кайта

ops$tkyte@ORA920.LOCALHOST> alter table emp add dname varchar2(30);

Table altered.

ops$tkyte@ORA920.LOCALHOST> update ( select emp.dname edname, dept.dname ddname
  2          from emp, dept
  3                where emp.deptno = dept.deptno )
  4   set edname = ddname;

14 rows updated.

ops$tkyte@ORA920.LOCALHOST> select type, id1, (select object_name from 
user_objects where object_id = id1) oname
  2    from v$lock
  3   where sid = (select sid from v$mystat where rownum=1)
  4      and type = 'TM'
  5  /

TY        ID1 ONAME
-- ---------- --------------------
TM      31715 EMP

Блокируются только строки таблицы, которую вы изменяете.

Комментарий читателя от 29 января 2003 года

scott@ORA734.WORLD> update
  2    ( select columnName, value
  3        from name, lookup
  4       where name.keyname = lookup.keyname
  5         and lookup.otherColumn = :other_value )
  6     set columnName = value
  7  /

1 row updated.

scott@ORA734.WORLD> update name
  2     set columnName = ( select value
  3                        from lookup
  4                       where lookup.keyname = name.keyname
  5                           and otherColumn = :other_value )
  6   where exists ( select value
  7                    from lookup
  8                   where lookup.keyname = name.keyname
  9                     and otherColumn = :other_value )
 10  /

1 row updated.

Том,

какой из этих двух запросов лучше с точки зрения производительности? Я предполагаю, что первый, но сомневаюсь. Если я ошибаюсь, то почему?

Ответ Тома Кайта

Оба плохи...

Нет, первый лучше

Хотя, постойте, второй лучше

Оба могут работать быстро, я думаю...

Их производительность будет зависеть от используемого оптимизатора, актуальности статистической информации, и т.п.

В общем случае, если "NAME" - очень маленькая таблица, а "LOOKUP" - очень большая, (но проиндексированная по keyname/othercolumn) - оператор с where exists вполне можно использовать.

Но обычно лучше изменять соединение.

Изменение главной таблицы на основе подчиненной (подчиненная - с составным ключом)

Не мог бы ты помочь мне сделать то, что я пытаюсь - изменить 2 или более столбца главной таблицы на основе данных в подчиненной, у которой первичный ключ - составной.

Пример:

Таблица: Food
(food_id    number primary key
 food_name  varchar2(100)
 calorie    number 
 fibre      number
 total_fat  number
)

Пример данных в таблице Food:
Food_id  food_name  calorie    fibre    total_fat
1        apple      null       null    null
2        beer       null       null    null

Таблица: Food_Portion
(food_id     number
 portion_id  number 
 qty         number
 weight      number
 calorie     number 
 fibre       number
 total_fat   number
 ) 
 
food_id + portion_id - первичный ключ

Пример данных в таблице Food_Portion:
Food_id    Portion_ID qty    weight calorie    fibre  total_fat
1          1          1      10     10         3      3    
1          2          2      15     20         5      2
1          3          100    100    60         10     9
2          1          1      10     5          0      0
2          2          .5     6      4          0      0
2          3          100    100    40         0      10

В результате изменения надо установить значения столбцов calorie, fibre и total_fat в таблице food_table равными значениям в таблице food_portion, где food_portion.qty = 100 и food_portion.weight = 100

Гарантируется, что для каждого food_id в таблице food_portion будет только одна строка с qty = 100 и weight = 100 (т.е. оператор select food_id from food_portion where qty = 100 and weight = 100 вернет ровно одну строку для каждого значения food_id)

Я выполнял следующий оператор update:

update ( select a1, b1 from a, b where a.id = b.id )
set a1 = b1 (this one I haven't tried)

Но получил сообщение об ошибке 'ORA-01779: cannot modify a column which maps to a non key-preserved table'

Потом я попробовал выполнить:

update t
  set ( f1, f2, f3 ) = 
        ( select a, b, c from t2 where t2.some_field = t.some_field )
 where exists ( select a, b, c from t2 where t2.some_field = t.some_field )
 
update a
set a1 = ( select b1 from b where b.id = a.id )
   where a.id in ( select id from b )

И оба оператора вернули сообщение об ошибке ORA-01427: single-row subquery returns more than one row

Меня интересует, можно ли выполнить такое изменение, не используя PL/SQL?

Ответ Тома Кайта

Если вы указываете:

where t2.some_field = t.some_field )

И получаете:

ORA-01427: single-row subquery returns more than one row

Значит, предположение о единственности строки для каждого food_id не оправдалось.

Но, в любом случае, если запрос:

select food_id
  from food_portion
 where qty = 100 and weight = 100
 group by food_id
 having count(*) > 1;

возвращает НОЛЬ строк (нет дублирующихся значений!), то:

ops$tkyte@ORA817DEV> update food
  2     set (calorie,fibre,total_fat) =
  3             (select calorie,fibre,total_fat
  4                    from food_portion fp
  5                   where fp.food_id = food.food_id
  6                     and fp.qty = 100
  7                     and fp.weight = 100 )
  8   where exists
  9             (select calorie,fibre,total_fat
 10                    from food_portion fp
 11                   where fp.food_id = food.food_id
 12                     and fp.qty = 100
 13                     and fp.weight = 100 )
 14  /

Выполняет соответствующее изменение.

Очень полезный пример

После того, как увидел способ изменения соединения, обнаружил, что он хорошо подходит для изменения одного источника данных с просмотром предварительных и окончательных значений:

scott@MSORA920>create table dept_demo as select * from dept;

Table created.

scott@MSORA920>SELECT deptno, dname, INITCAP(dname) Iname, loc, INITCAP(loc) 
Iloc FROM dept_demo;

    DEPTNO DNAME          INAME          LOC           ILOC
---------- -------------- -------------- ------------- -------------
        10 ACCOUNTING     Accounting     NEW YORK      New York
        20 RESEARCH       Research       DALLAS        Dallas
        30 SALES          Sales          CHICAGO       Chicago
        40 OPERATIONS     Operations     BOSTON        Boston

scott@MSORA920>edit
Wrote file afiedt.buf

  1  UPDATE(
  2  SELECT deptno, dname, INITCAP(dname) Iname, loc, INITCAP(loc) Iloc FROM 
dept_demo
  3* ) SET dname = Iname, loc = Iloc
scott@MSORA920>/

4 rows updated.

scott@MSORA920>edit
Wrote file afiedt.buf

  1* SELECT deptno, dname, INITCAP(dname) Iname, loc, INITCAP(loc) Iloc FROM 
dept_demo
scott@MSORA920>/

    DEPTNO DNAME          INAME          LOC           ILOC
---------- -------------- -------------- ------------- -------------
        10 Accounting     Accounting     New York      New York
        20 Research       Research       Dallas        Dallas
        30 Sales          Sales          Chicago       Chicago
        40 Operations     Operations     Boston        Boston

scott@MSORA920>

Спасибо :)

Комментарий читателя от 1 мая 2003 года

Том,

Если в твоем исходном примере вместо:

Update...
SET    columnName = Value;

Написать:

Update...
SET    Value = columnName;

Я получаю сообщение об ошибке "ORA-01779: cannot modify a column which maps to a non key-preserved table" (см. ниже).

Разме в запросе columnName не возвращает тоже всего одну запись? Почему так не срабатывает?

SQL> CREATE TABLE name (
  2  keyname    INT,
  3  columnName VARCHAR2(25)
  4  );

Table created.

SQL> CREATE TABLE lookup (
  2  keyname     INT PRIMARY KEY,
  3  value       VARCHAR2(25),
  4  otherColumn INT
  5  );

Table created.

SQL> insert into name values ( 100, 'Original Data' );

1 row created.

SQL> insert into name values ( 200, 'Original Data' );

1 row created.

SQL> insert into lookup values ( 100, 'New Data', 1 );

1 row created.

SQL> commit;

Commit complete.

SQL> UPDATE (
  2     SELECT a.columnName, b.value
  3     FROM   name a, lookup b
  4     WHERE  a.keyname = b.keyname AND
  5            b.otherColumn = 1)
  6  SET columnName = value;

1 row updated.

SQL> rollback;

Rollback complete.

SQL> UPDATE (
  2     SELECT a.columnName, b.value
  3     FROM   name a, lookup b
  4     WHERE  a.keyname = b.keyname AND
  5            b.otherColumn = 1)
  6  SET value = columnName;
SET value = columnName
    *
ERROR at line 6:
ORA-01779: cannot modify a column which maps to a non key-preserved table

Ответ Тома Кайта

SQL> CREATE TABLE name (
  2  keyname    INT,
  3  columnName VARCHAR2(25)
  4  );

Table created.

SQL> CREATE TABLE lookup (
  2  keyname     INT PRIMARY KEY,
  3  value       VARCHAR2(25),
  4  otherColumn INT
  5  );

insert into name values ( 1, 'x' );
insert into name values ( 1, 'y' );

insert into lookup values ( 1, null, null );

А теперь, какой результат должен получиться после изменения:

SQL> UPDATE (
  2     SELECT a.columnName, b.value
  3     FROM   name a, lookup b
  4     WHERE  a.keyname = b.keyname AND
  5            b.otherColumn = 1)
  6  SET value = columnName;

Столбец value будет иметь значение x или y?

Чтобы избавиться от двусмысленности, необходим первичный ключ по столбцам таблиц "lookup", по которым выполняется соединение - у вас его нет.


Страница сайта http://test.interface.ru
Оригинал находится по адресу http://test.interface.ru/home.asp?artId=23931