ОБНОВЛЕНИЕ SQL Server с WHERE, охватывающим более 2 таблиц

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

У меня 4 таблицы затронуты (/ использованы) в запросе.

  • [Заказы]
  • [StatusHistoryForOrder]
  • [StatusHistory]
  • [Статусы]

Мне нужно обновить поле [Заказы]. [OrderStatusID], которое является внешним ключом для [Статусы]. (Таким образом, фактически изменяется состояние заказа. Таблица [StatusHistoryForOrder] является таблицей связывания с [StatusHistory] и содержит только 2 столбца.

  • [StatusHistoryForOrder]. [OrderId]
  • [StatusHistoryForOrder]. [OrderStatusHistoryid]

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

Таблица [StatusHistory] состоит из нескольких столбцов:

  • [StatusHistory]. [OrderStatusHistoryId]
  • [StatusHistory]. [OrderStatusId]
  • [StatusHistory]. [Дата]
  • [StatusHistory]. [Сообщение]

[StatusHistory]. [OrderStatusId] также является внешним ключом для [Статусы].

В запросе на обновление мне нужно обновить статус заказа до статуса 16. Но только для строк, которые теперь имеют статус 1 и старше 60 дней. Я знаю, что могу проверить дату с помощью функции

DATEDIFF(DD,[StatusHistory].[Date],GETDATE()) > 60

Но как реализовать этот запрос, если поля даты нет в заказах. И чтобы установить новую [StatusHistory], необходимо создать новую строку для этой таблицы, а для таблицы [StatusHistoryForOrder] также требуется новая строка, и идентификатор этой строки должен быть установлен в строке таблицы [Заказы].

Кто-нибудь знает как это сделать? Я новичок в SQL Server (или SQL, если на то пошло), и я абсолютно не знаю, с чего начать.

Вывод:

Мне нужна хранимая процедура, которая сначала проверяет каждую строку в [Заказы], если [StatusHistory]. [Date] (который связан с заказом с помощью внешних ключей) этого заказа старше 60. Если он старше, то создается новый StatusHistory строка должна быть вставлена ​​с текущей датой и статусом 16. Затем в [StatusHistoryForOrder] должна быть вставлена ​​новая строка с новым идентификатором statusHistory, установленным в [StatusHistoryForOrder]. [OrderStatusHistoryid], и идентификатором заказа, установленным в [StatusHistoryForOrder]. [Номер заказа]. И последнее, но не менее важное: для [Orders]. [OrderStatusID] также необходимо установить значение 16.

Запрос на выбор для выбора даты и статуса заказа:

SELECT     TOP (100) PERCENT 
    dbo.Orders.OrderID, 
    dbo.Statuses.Description AS Status, 
    dbo.StatusHistory.Date
FROM         
    dbo.Orders 
INNER JOIN
    dbo.Statuses 
ON 
    dbo.Orders.OrderStatusID = dbo.Statuses.StatusId 
INNER JOIN
    dbo.StatusHistoryForOrder 
ON 
    dbo.Orders.OrderID = dbo.StatusHistoryForOrder.OrderId 
INNER JOIN
    dbo.StatusHistory 
ON 
    dbo.StatusHistoryForOrder.OrderStatusHistoryid = dbo.StatusHistory.OrderStatusHistoryId
WHERE     
    (dbo.Statuses.StatusId = 1) 
AND 
    (DATEDIFF(DD, dbo.StatusHistory.Date, GETDATE()) > 60)

ОБНОВЛЕНИЕ для @marc_s:

Информация о столбце

Кто-нибудь может мне с этим помочь?


sql-server stored-procedures sql-server-2008-r2
person SynerCoder    schedule 16.03.2011    source источник
comment
@marc_s Microsoft SQL Server 2008 R2. Устанавливается с помощью WPI. Также используется: SQL Server Management Studio   -  person SynerCoder    schedule 16.03.2011
comment
У вас нет бизнес-уровня?   -  person Stefan Steinegger    schedule 16.03.2011
comment
@Stefan Steinegger ,, я понятия не имею, о чем вы говорите. Как я уже сказал, я новичок в этом.   -  person SynerCoder    schedule 16.03.2011
comment
@SynerCoder: я имею в виду программу, написанную на языке программирования высокого уровня, которая реализует бизнес-логику и отвечает за согласованность.   -  person Stefan Steinegger    schedule 16.03.2011
comment
@SynerCoder: если у вас есть бизнес-логика в asp, вы можете написать логику там вместо хранимой процедуры. Если только вы не вынуждены писать SP по соображениям производительности.   -  person Stefan Steinegger    schedule 16.03.2011

Ответы (2)


arrow_upward
1
arrow_downward

Попробуйте это CTE (Common Table Expression), чтобы найти все эти порядки - работает ли оно, правдоподобны ли результаты? (это пока ничего не обновляет - пока просто ВЫБИРАЕТЕ):

USE (your database name here)
GO

DECLARE @OrdersToUpdate TABLE (OrderID INT, StatusHistoryID INT, StatusDate DATETIME)

;WITH RelevantOrders AS
(
    SELECT 
       o.OrderId, sh.Date
    FROM dbo.Orders o
    INNER JOIN dbo.StatusHistoryForOrder ho ON ho.OrderId = o.OrderId
    INNER JOIN dbo.StatusHistory sh ON ho.OrderStatusHistoryid = sh.OrderStatusHistoryid 
    WHERE
       sh.Date <= DATEADD(D, -60, GETDATE())   -- older than 60 days back from today
       AND o.OrderStatusID = 1                 -- status = 1
)
INSERT INTO @OrdersToUpdate(OrderID, StatusDate)
   SELECT OrderID, [Date] 
   FROM RelevantOrders

BEGIN TRANSACTION
BEGIN TRY
    DECLARE @OrderIDToInsert INT,         -- OrderID to process
            @InsertedStatusHistoryID INT  -- new ID of the inserted row in StatusHistory

    -- grab the first OrderID that needs to be processed
    SELECT TOP 1 @OrderIDToInsert = OrderID
    FROM @OrdersToUpdate
    WHERE StatusHistoryID IS NULL
    ORDER BY OrderID

    -- as long as there are still more OrderID to be processed ....
    WHILE @OrderIDToInsert IS NOT NULL
    BEGIN
       PRINT 'Now inserting new StatusHistory entry for OrderID = ' + CAST(@OrderIDToInsert AS VARCHAR(10))

       INSERT INTO dbo.StatusHistory(OrderStatusID, [Date], [Message])
       VALUES(16, GETDATE(), 'Bulk Insert/Update operation')   -- enter here whatever you want to store 

       SELECT @InsertedStatusHistoryID = SCOPE_IDENTITY();   -- grab newly inserted ID 

       PRINT 'New StatusHistory entry inserted with ID = ' + CAST(@InsertedStatusHistoryID AS VARCHAR(10))

       UPDATE @OrdersToUpdate
       SET StatusHistoryID = @InsertedStatusHistoryID
       WHERE OrderID = @OrderIDToInsert

       -- safety - reset @OrderIDToInsert to NULL so that we'll know when we're done
       SET @OrderIDToInsert = NULL

       -- read next OrderID to be processed
       SELECT TOP 1 @OrderIDToInsert = OrderID
       FROM @OrdersToUpdate
       WHERE StatusHistoryID IS NULL
       ORDER BY OrderID
    END 

    -- insert into the StatusHistoryForOrder table
    INSERT INTO dbo.StatusHistoryForOrder(OrderID, OrderStatusHistoryID)
        SELECT OrderID, StatusHistoryID
        FROM @OrdersToUpdate

    -- update your Orders to status ID = 16
    UPDATE dbo.Orders
    SET OrderStatusID = 16 
    FROM @OrdersToUpdate upd
    WHERE dbo.Orders.OrderID = upd.OrderID

    COMMIT TRANSACTION
END TRY
BEGIN CATCH
   SELECT 
       ERROR_NUMBER() AS ErrorNumber,
       ERROR_SEVERITY() AS ErrorSeverity,
       ERROR_STATE() AS ErrorState,
       ERROR_PROCEDURE() AS ErrorProcedure,
       ERROR_LINE() AS ErrorLine,
       ERROR_MESSAGE() AS ErrorMessage

   ROLLBACK TRANSACTION
END CATCH

Этот CTE в основном объединяет вашу Orders таблицу с StatusHistory таблицей (через промежуточную таблицу ссылок) и выбирает интересующие вас значения (надеюсь!).

person marc_s    schedule 16.03.2011
comment
@marc_s Ваш запрос не сработал, не знаю почему. Но у меня может быть кое-что похожее недавно: - person SynerCoder; 16.03.2011
comment
@marc_s Снова нет строк. Понятия не имею, почему. Даже если я удалю все предложение where вашего запроса, по-прежнему нет строк. ?!?!? - person SynerCoder; 16.03.2011
comment
@marc_s извините извините извините. Как я уже сказал, я новичок в этом. Я отследил ошибку до databaseName.dbo.table, а не только до dbo.table. Теперь ваш запрос работает и возвращает 656 строк. - person SynerCoder; 16.03.2011
comment
@marc_s Я не только хочу обновить эти строки, я хочу, чтобы в строки в [StatusHistoryForOrder] и [StatusHistory] была вставлена ​​новая строка. Так что я могу вспомнить статистическую историю. - person SynerCoder; 16.03.2011
comment
@synercoder: конечно - по крайней мере, теперь мы получаем какие-то данные! :-) Итак, если я правильно понимаю, с этими заказами вы теперь хотите 1) обновить их статус до 16 и 2) вставить новую строку в таблицу StatusHistory (и StatusHistoryForOrder), верно? Все, правда ?? - person marc_s; 16.03.2011
comment
@synercoder: Хорошо, опубликовал первое решение - можете ли вы его попробовать ?? (если возможно, сначала на тестовом наборе данных!) - person marc_s; 16.03.2011
comment
не помогло. он напечатал: (0 затронутых строк) (0 затронутых строк) (0 затронутых строк) - person SynerCoder; 16.03.2011
comment
Только что увидел ошибку: оператор INSERT конфликтует с ограничением FOREIGN KEY FK_StatusHistory_Statuses. Конфликт произошел в мастере базы данных, таблица dbo.Statuses, столбец StatusId. - person SynerCoder; 16.03.2011
comment
@synercoder: хорошо, так что вам, вероятно, придется вставить некоторые другие значения для StatusID в таблицу StatusHistory - я не знаю, какими могут быть эти значения ..... также: ваша таблица Statuses действительно находится в master < / b> база данных ??? Вам, вероятно, понадобится USE (my database name here) перед выполнением моего кода, чтобы быть в правой базе данных (где у вас есть свои таблицы!) - person marc_s; 16.03.2011
comment
@marc_s использовал вещь: теперь ошибка: нарушение ограничения PRIMARY KEY 'PK_StatusHistoryForOrder'. Невозможно вставить повторяющийся ключ в объект 'dbo.StatusHistoryForOrder'. Строка 58 - person SynerCoder; 16.03.2011
comment
@synercoder: сейчас ломаю голову - все эти значения должны быть уникальными и новыми, поэтому я немного озадачен этим сообщением ... - person marc_s; 16.03.2011
comment
@synercoder: у меня есть догадка - не могли бы вы использовать мой код до того, как CTE вставит данные в переменную таблицы? Вы можете сделать SELECT * FROM @OrdersToUpdate сразу после этого ?? Вы видите там повторяющиеся идентификаторы OrderID? - person marc_s; 16.03.2011

arrow_upward
0
arrow_downward

Эта конкретная проблема кажется разрешимой только с помощью операций с множеством.

DECLARE @Orders TABLE (ID int, rownum int IDENTITY);
DECLARE @StatusHistory TABLE (ID int, rownum int IDENTITY);

/* get the list of orders with expired statuses */
INSERT INTO @Orders (ID)
SELECT o.OrderID
FROM Orders o
  INNER JOIN StatusHistoryForOrder shfo ON o.OrderID = shfo.OrderId
  INNER JOIN StatusHistory sh ON shfo.OrderStatusHistoryid = sh.OrderStatusHistoryId
GROUP BY o.OrderID
HAVING DATEDIFF(DD, MAX(sh.Date), GETDATE()) > 60

/* add so many new rows to StatusHistory and remember the new IDs */
INSERT INTO StatusHistory (OrderStatusId, Date, Message)
OUTPUT inserted.OrderStatusHistoryId INTO @StatusHistory (ID)
SELECT
  16,
  GETDATE(),
  'Auto-inserted as the previous status has expired'
FROM @Orders

/* join the two temp lists together and add rows to StatusHistoryForOrder */
INSERT INTO StatusHistoryForOrder (OrderId, OrderStatusHistoryid)
SELECT o.ID, sh.ID
FROM @Orders o
  INNER JOIN @StatusHistory sh ON o.rownum = sh.rownum

/* finally update the statuses in Orders */
UPDATE Orders
SET OrderStatusID = 16
FROM @Orders o
WHERE Orders.OrderID = o.ID

Конечно, это должно быть тело отдельной транзакции.

person Andriy M    schedule 16.03.2011
comment
Ваше решение возвращает: (затронуто 18679 строк), в то время как только 234 строки должны быть затронуты. - person SynerCoder; 17.03.2011