I am trying to create a stored procedure in SQL Server that will essentially update a table given a condition.

Let's say I have a table with Orders

A table we want to insert into let's call this InsertOrder Table

What I am trying to do is look through the Orders table where the Product ID = 'BEA10' and update a single row in the InsertOrder table and set it to the Order ID where we found the Product ID of 'BEA10' as long as that Order ID in the InsertOrder info table is NULL

I guess that would mean that if we run the stored proc again it should ignore already inserted Order ID's.

I am fairly new to SQL so I tried to build up some form of code but it seems way off

ALTER PROCEDURE [dbo].[InsertOrder] 
    
AS

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @ORDERS_PRODUCTID nvarchar(50);
DECLARE @ORDERS_ORDERID int;

SELECT @ORDERS_PRODUCTID = ProductID from ORDERS
SELECT @ORDERS_ORDERID = OrderID from ORDERS

IF(@ORDERS_PRODUCTID = 'BEA10' and @ORDERS_ORDERID not in (SELECT OrderID from InsertOrder)) 

BEGIN
    

    
    UPDATE InsertOrder SET OrderID = @ORDERS_ORDERID where OrderID is null
    
END
GO

CodePudding user response：

hope it works

DECLARE @ORDERS_PRODUCTID nvarchar(50);
DECLARE @ORDERS_ORDERID int;

SET @ORDERS_ORDERID = (SELECT OrderID 
                        FROM ORDERS O 
                        WHERE ProductID='BEA10' 
                            AND NOT EXISTS (SELECT 1 FROM InsertOrder WHERE OrderID=O.OrderID));

UPDATE InsertOrder 
SET OrderID = @ORDERS_ORDERID 
WHERE Code=(SELECT TOP 1 Code FROM InsertOrder WHERE OrderID is null);