我可以使用 1 个 SQL 语句更新多条记录，其中更新值取决于正在更新的记录吗？

Question

假设有 2 个表：

1. STATES（保存可用状态列表）- 键字段 = STATE_ID。另一个键是 DOMAIN_ID、STATE_NAME。因此，存在具有相同 STATE_NAME 但不同 DOMAIN_ID 的记录。
2. OBJECTS（保存所有对象的列表，每个对象都有自己的状态）-键字段= OBJECT_ID。还包含一个字段 STATE_ID。

假设我有一个大约 1000 个对象的列表，这些对象需要在各自的域中更新为“CLOSED”状态。

对于一个单一的对象（比如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

Is it possible to use one statements to update multiple object?问题的关键似乎是我无法绕过在 SQL 语句中的两个位置定义 OBJECT_ID。

由于显而易见的原因，以下声明不起作用：

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)

有人能给我一个提示，告诉我可以做些什么来解决这个问题吗？

谢谢。

Answer 1

您可以使用表名 OBJECTS 因为它在范围内，例如

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
                                      )
                 );

Answer 2

SQL Server、MS Access、（MySQL？）解决方案：

我认为这可以解决问题。您可能需要首先用 SELECT * 替换 UPDATE 和 SET 子句，并检查结果集是否正确：

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

Answer 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在对象表中。