Блог

Главная - Вопросы по программированию - Могу ли я обновить несколько записей с помощью 1 инструкции SQL, где значение обновления зависит от обновляемой записи?

Могу ли я обновить несколько записей с помощью 1 инструкции SQL, где значение обновления зависит от обновляемой записи?

#sql #sql-update #records

#sql #sql-update #записи

Вопрос:

Учитывая, что существует 2 таблицы:

  1. СОСТОЯНИЯ (содержит список доступных состояний) — ключевое поле = STATE_ID. Другой ключ — DOMAIN_ID, STATE_NAME. Таким образом, существуют записи с одинаковым STATE_NAME, но разным DOMAIN_ID.
  2. ОБЪЕКТЫ (содержит список всех объектов, каждый со своим собственным состоянием) — ключевое поле = OBJECT_ID. Также содержит поле STATE_ID.

Предположим, у меня есть список из примерно 1000 объектов, которые необходимо обновить до состояния «ЗАКРЫТО» в их соответствующих доменах.

Для одного объекта (скажем, OBJECT_ID 12345) я могу использовать инструкцию SQL:

 update OBJECTS
set STATE_ID =
(
  select STATE_ID from STATES
  where STATE_NAME= 'CLOSED'
  and DOMAIN_ID =
  (
    select DOMAIN_ID from STATES a, OBJECTS b
    where a.STATE_ID = b.STATE_ID and b.OBJECT_ID = 12345)
  )
) where OBJECT_ID = 12345

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

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

 update OBJECTS
set STATE_ID =
(
  select STATE_ID from STATES
  where STATE_NAME= 'CLOSED'
  and DOMAIN_ID =
  (
    select DOMAIN_ID from STATES a, OBJECTS b
    where a.STATE_ID = b.STATE_ID and b.OBJECT_ID in
    (
      select distinct OBJECT_ID from OBJECTS_TO_UPDATE
    )
  )
) where OBJECT_ID in (select distinct OBJECT_ID from OBJECTS_TO_UPDATE)

Кто-нибудь может дать мне подсказку о том, что я мог бы сделать, чтобы обойти это?

Спасибо.

Комментарии:

1. Поскольку некоторые СУБД допускают определенный синтаксис, которого нет у других, что вы используете? (Oracle, MySQL и т.д.?)

Ответ №1:

Вы можете использовать объекты имен таблиц, поскольку они находятся в области видимости, например 

 UPDATE OBJECTS
  SET STATE_ID = (
                  SELECT STATE_ID 
                    FROM STATES
                   WHERE STATE_NAME = 'CLOSED'
                         AND DOMAIN_ID = (
                                          SELECT DOMAIN_ID 
                                            FROM STATES a, 
                                                 OBJECTS b
                                           WHERE a.STATE_ID = b.STATE_ID 
                                                 AND b.OBJECT_ID = OBJECTS.OBJECT_ID
                                         )
                 )
 WHERE EXISTS (
               SELECT * 
                 FROM STATES
                WHERE STATE_NAME = 'CLOSED'
                      AND DOMAIN_ID = (
                                       SELECT DOMAIN_ID 
                                         FROM STATES a, 
                                              OBJECTS b
                                        WHERE a.STATE_ID = b.STATE_ID 
                                              AND b.OBJECT_ID = OBJECTS.OBJECT_ID
                                      )
                 );

Комментарии:

1. Вы функционально изменили то, что хочет сделать OP. Только подмножество таблицы OBJECTS нуждается в обновлении (те, у кого есть OBJECT_ID в таблице OBJECTS_TO_UPDATE)

2. @Dems: предполагалось, что это подчеркнет, как на таблицу можно ссылаться в области видимости, но для полноты (и излишества) я обновил свой ответ.

3. Ммм, разве предложение WHERE не должно быть просто в соответствии с OPS? where OBJECT_ID in (select distinct OBJECT_ID from OBJECTS_TO_UPDATE)

4. @Dems: Я смотрю только на первый запрос в вопросе (научите человека ловить рыбу и все такое).

5. однажды, спасибо за ваше предложение, и оно протестировано на работоспособность. Что касается комментария dems, я увидел оригинальный ответ, решил, что предложение where необходимо обновить, и изменил его для моего теста, так что никакого вреда не было. Я просто просил кого-нибудь указать мне правильное направление в любом случае, а не кого-то дать мне точный SQL, который мне нужен. Но спасибо за исправление вашего ответа, поскольку это поможет другим читателям.

Ответ №2:

SQL Server, MS Access, (MySQL?) решение:

Я думаю, что это помогает. Возможно, вы захотите заменить предложения UPDATE and SET на SELECT * first и проверить, что результирующий набор выглядит правильно:

 update o
set STATE_ID = s_closed.STATE_ID
from
   Objects o
      inner join
   States s_current
      on
         o.STATE_ID = s.STATE_ID
      inner join
   States s_closed
      on
         s_current.DOMAIN_ID = s_closed.DOMAIN_ID and
         s_closed.STATE_NAME = 'Closed'
      inner join
   OBJECTS_TO_UPDATE otu
      on
         otu.OBJECT_ID= o.OBJECT_ID

Комментарии:

1. Это должно работать в MS-SQL-Server, но не в Oracle (например). Полностью зависит от того, какую СУБД использует OP, я думаю…

2. @Dems — верно, я добавлю примечание вверху — но обратите внимание, что мы работаем с двумя разными строками из таблицы СОСТОЯНИЙ, и я думаю, что это соответствует тому, что есть у OP в их первом запросе — так что ваш ответ тоже нуждается в небольшой доработке 🙂

Ответ №3:

Не зная больше о структуре ваших таблиц, я не могу быть уверен в том, какой SQL вам нужен. Но подзапрос для получения нового STATE_ID может (как было сказано onedaywhen) ссылаться на таблицу, которую вы обновляете…

Например…

 update
  OBJECTS
set
  STATE_ID =
    (
      select
        NEW_STATE.STATE_ID
      from
        STATES    AS OLD_STATE
      inner join
        STATES    AS NEW_STATE
          ON NEW_STATE.DOMAIN_ID = OLD_STATE.DOMAIN_ID
      where
        OLD_STATE.STATE_ID = OBJECTS.STATE_ID
        AND NEW_STATE.STATE_NAME = 'CLOSED'
    )
where
  OBJECT_ID in (select distinct OBJECT_ID from OBJECTS_TO_UPDATE)

Таким образом, вы можете гарантировать, что вложенный запрос возвращает только один STATE_ID для любой заданной записи в таблице ОБЪЕКТОВ.