SQL Server UPDATE with WHERE spanning over 2 tables

Question

I have a SQL Server database and I need to manually do an update query. There for no solutions using any programming language can be used.(stored procedures can be used)

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

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

I need to update the field [Orders].[OrderStatusID] which is a foreign key to [Statuses]. (So actually changing the state of the order. The table [StatusHistoryForOrder] is a linking table to [StatusHistory] and only contains 2 colums.

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

Don't say that this is not logically cause I already know that. The company who designed the database is a complete retarded company but the database is now too large to set things straight and there is neither the time or money to do it.

The [StatusHistory] table has multiple columns:

• [StatusHistory].[OrderStatusHistoryId]
• [StatusHistory].[OrderStatusId]
• [StatusHistory].[Date]
• [StatusHistory].[Message]

The [StatusHistory].[OrderStatusId] is also a foreign key to [Statuses].

In the update query I need to update the status of the order to status 16. But only on rows that now have status 1 and are older then 60 days. I know I can check the date by using the function

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

But how to implement this query if the date field is not in the orders. And to set the new [StatusHistory] a new row has to be made for that table and the [StatusHistoryForOrder] table also needs a new row and the ID of that row needs to be set in the [Orders] table row.

Does anyone know how to do this? I am fairly new to SQL Server (or SQL for that matter) and I have absolutly no clue where to begin.

Conclusion:

I need a stored procedure that first checks every row in [Orders] if the [StatusHistory].[Date] (which is linked to the order using foreign keys) of that order is older that 60. If it is older then a new StatusHistory row must be inserted with the current date and status 16. Then in [StatusHistoryForOrder] a new row must be inserted with the new ID of the statusHistory been set in [StatusHistoryForOrder].[OrderStatusHistoryid] and the order id set in [StatusHistoryForOrder].[OrderId]. And last but not least: The [Orders].[OrderStatusID] also needs to be set to 16.

---

A select query to select the date and status of the order:

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 For @marc_s:

---

Can anyone help me with that?

Answer 1

Try this CTE (Common Table Expression) to find all those orders - does it work, are the results plausible? (this doesn't update anything just yet - just SELECTing for now):

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

This CTE basically joins your Orders table to the StatusHistory table (via the intermediate link table) and selects the values you're interested in (hopefully!).