Geek asks and answers

SQL Server UPDATE with WHERE spanning more than 2 tables

I have a SQL Server database and I need to manually execute the update request. There are no solutions using any programming language (stored procedures can be used)

I have 4 tables affected (/ used) in a query.

  • [Orders]
  • [StatusHistoryForOrder]
  • [StatusHistory]
  • [Statuses]

I need to update the [Orders] field. [OrderStatusID], which is the foreign key for [Statuses]. (So, actually changing the state of the order. The [StatusHistoryForOrder] table is the middleware table in the [StatusHistory] and contains only 2 columns.

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

Do not say that this is not logical, because I already know this. The company that developed the database is a completely backward company, but the database is now too large to install everything in a row, and there is no time and money for this.

The [StatusHistory] table has several columns:

  • [StatusHistory]. [OrderStatusHistoryId]
  • [StatusHistory]. [OrderStatusId]
  • [StatusHistory]. [The date]
  • [StatusHistory]. [Message]

[StatusHistory]. [OrderStatusId] is also a foreign key for [Statuses].

In the update request, I need to update the order status to status 16. But only in the lines that now have the status of 1 and older than 60 days. I know that I can check the date using the function

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

, . [StatusHistory], , [StatusHistoryForOrder] , [].

- , ? SQL Server ( SQL, ), , .

:

, [], [StatusHistory]. [] ( ) 60. , StatusHistory ​​ 16. [StatusHistoryForOrder] ​​ statusHistory, [StatusHistoryForOrder]. [OrderStatusHistoryid] , [ StatusHistoryForOrder]. [OrderId]. , : []. [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)

UPDATE @marc_s:

Column info

- ?

+3
2

CTE (Common Table Expression), - , ? ( - SELECT ):

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 ( ) , (,!).

+1

.

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

, .

0

More articles:


All Articles