Обновление не работает должным образом с производной таблицей

#sql #sql-server-2008

Вопрос:

Пожалуйста, смотрите Запрос ниже:

 update dbusns 
set thisdate = created 
from 
    (select 
        MAX(created) AS CREATED, DBCUSTODY.REFERENCE 
     from dbusns 
     inner join [server].Custody.DBO.dbcustody on dbusns.urns = dbcustody.reference
     where dbusns.datasetname = 'CUSTODY' 
     group by dbcustody.reference) As CustodyDateTable 
WHERE 
   dbusns.urns = CustodyDateTable.reference 
   and dbusns.urns = '1'

Следующий запрос возвращает 01/01/2011 :

 select 
    MAX(created) AS CREATED, DBCUSTODY.REFERENCE 
from 
    dbusns 
inner join 
    server.database.DBO.dbcustody on dbusns.urns = dbcustody.reference
where 
    dbusns.datasetname = 'CUSTODY' AND DBCUSTODY.REFERENCE = '1'

Однако следующий запрос возвращает 31/10/2011 (после выполнения запроса 1):

 select 
    THISDATE 
from 
    dbusns 
where 
    datasetname = 'CUSTODY' AND URNS = '1' --QUERY 3

Приведенный ниже запрос возвращает две строки (31/10/2011 и 01/11/2011):

 select created 
from [server].Custody.DBO.dbcustody 
where reference = '1'

Почему запрос 3 возвращает 31/10/2011? Он должен вернуть 01/11/2011? Это как-то связано с планом выполнения / связанным сервером?

1. Возможно ли, чтобы вы SELECT * INTO #dbusns FROM dbusns WHERE datasetname = 'CUSTODY' AND URNS = '1' и SELECT * INTO #dbcustody FROM from [server].Custody.DBO.dbcustody WHERE reference = '1' , а затем, с целью тестирования, попытались выполнить одно и то же обновление для этих двух таблиц и проверить результат?

2. @Andriy M, спасибо. Это хорошая идея. Я попробую это. 1

3. Учитывая ваши примерные данные, максимальная дата — это возвращаемая дата. Вы действительно хотите MIN() вместо MAX() ?

4. @andriy M, это работает, если dbusns и dbcustody находятся на одном сервере базы данных. Сбой в работе кросс-базы данных. Интересно, что один сервер — sql 2005, а другой — sql 2008.

Ответ №1:

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

 UPDATE D
SET thisdate = T.created 
FROM dbusns D
INNER JOIN (SELECT  MAX(created) created,
                    C.reference 
            FROM dbusns 
            INNER JOIN [server].Custody.DBO.dbcustody C
                ON dbusns.urns = dbcustody.reference
            WHERE dbusns.datasetname = 'CUSTODY' 
            GROUP BY dbcustody.reference) T
    ON D.urns = T.reference
WHERE D.urns = 1

1. Спасибо. Я также пробовал этот запрос. Это не решило проблему.

2. @w0051977 Можете ли вы уточнить, что это значит?, потому что он должен делать то, что вы хотите

3. Спасибо. dbcustody содержит две строки для URN=1 (31/10/2011 и 01/11/2011). Ваш запрос выше обновляет dbusns.thisdate до 31/10/2011 (а не 01/11/2011) для URN 1.

4. @w0051977 Я не понимаю, как это может быть актуально, но, поскольку я не вижу ваших фактических данных….

5. @w0051977 Извините, больше не могу вам помочь, не понимая ваших фактических данных

Ответ №2:

Я думаю, что ваш update синтаксис правильный, за исключением одной маленькой вещи. Рассмотрим эту строку:

 update dbusns set thisdate = created from ( . . .

created Столбец — я предполагаю — находится внутри dbusns . Таким образом, это просто установка thisdate на созданное значение в той же таблице.

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

 update dbusns set thisdate = CustodyDateTable.created from ( . . .