Transform Select To Update

Mitch McConnell 41 Reputation points
2022-08-20T03:04:09.793+00:00

how can I turn this select into an update statement so that for each sku in the OrderData table where the SUM(Quantity) is greater than the QtyAvaliable in SkuData table for that individual Sku we update the field enoughtofulfill to no

Create Table SkuData  
(  
  id int not null primary key,  
  sku varchar(500) null,  
  qtyavilable int null  
)  
  
CREATE TABLE OrderData  
(  
 ID int NOT NULL PRIMARY KEY,  
 OrderKey varchar(max) NOT NULL,  
 SKU varchar(max) NOT NULL,  
 Quantity nvarchar(50) NOT NULL, -- why is quantity nvarchar?  
 FileUploaded datetime2(7) NULL  
);  
  
  
INSERT SkuData   
( Id  
, Sku  
, QtyAvailable  
)   
VALUES (1, 'A', 0)  
, (2, 'B', 3)  
, (3, 'C', 5)  
;  
  
INSERT dbo.OrderData  
( ID  
, OrderKey  
, SKU  
, Quantity  
, FileUploaded  
)   
VALUES (1, 'Darc', 'A', 1, '2022-08-15T08:22:42.3439717')  
, (2, 'Asif', 'A', 1, '2022-08-15T08:22:42.4474510')  
, (3, 'John', 'A', 1, '2022-08-15T08:22:42.4477304')  
, (4, 'Ricc', 'B', 1, '2022-08-15T08:22:42.4477682')  
, (5, 'Mega', 'B', 1, '2022-08-15T08:22:42.4478116')  
, (6, 'Step', 'B', 1, '2022-08-15T08:22:42.4478387')  
, (7, 'Scot', 'C', 1, '2022-08-15T08:22:42.4478634')  
, (8, 'Mich', 'C', 1, '2022-08-15T08:22:42.4478863')  
, (9, 'Luis', 'C', 1, '2022-08-15T08:22:42.4479144')  
 ;  
  
Alter Table OrderData Add enoughtofulfill varchar(100);  
  
SELECT o.ID AS OrderID  
, o.OrderKey  
, o.SKU  
, o.Quantity AS QtyOrdered  
, o.FileUploaded  
, sku.ID AS InventoryID  
, sku.QtyAvailable  
, SUM(TRY_CONVERT(int, o.Quantity))   
      OVER (PARTITION BY o.SKU   
            ORDER BY o.FileUploaded) AS QtyRequired  
FROM   OrderData o  
INNER JOIN  
       SkuData sku  
ON     o.SKU = sku.Sku  
ORDER BY o.SKU  
, o.FileUploaded;  

--------------
EDIT-------------
If I change DDL To This....then only ONE row for B get's updated to no, bc it is 3 - 2 relationship. If the TOTAL is less I want all rows updated to no

    Create Table SkuData  
     (  
       id int not null primary key,  
       sku varchar(500) null,  
       QtyAvailable int null  
     )  
          
     CREATE TABLE OrderData  
     (  
         ID int NOT NULL PRIMARY KEY,  
         OrderKey varchar(max) NOT NULL,  
         SKU varchar(max) NOT NULL,  
         Quantity nvarchar(50) NOT NULL, -- why is quantity nvarchar?  
         FileUploaded datetime2(7) NULL  
     );  
          
    INSERT SkuData   
     (    Id  
     ,    Sku  
     ,    QtyAvailable  
     )   
     VALUES    (1, 'A', 14)  
     ,    (2, 'B', 2)  
     ,    (3, 'C', 5)  
     ;  
          
       
    INSERT dbo.OrderData  
     (    ID  
     ,    OrderKey  
     ,    SKU  
     ,    Quantity  
     ,    FileUploaded  
     )   
     VALUES    (1,    'Darc',    'A',    1,    '2022-08-15T08:22:42.3439717')  
     ,    (2,    'Asif',    'A',    1,    '2022-08-15T08:22:42.4474510')  
     ,    (3,    'John',    'A',    1,    '2022-08-15T08:22:42.4477304')  
     ,    (4,    'Ricc',    'B',    1,    '2022-08-15T08:22:42.4477682')  
     ,    (5,    'Mega',    'B',    1,    '2022-08-15T08:22:42.4478116')  
     ,    (6,    'Step',    'B',    1,    '2022-08-15T08:22:42.4478387')  
     ,    (7,    'Scot',    'C',    1,    '2022-08-15T08:22:42.4478634')  
     ,    (8,    'Mich',    'C',    1,    '2022-08-15T08:22:42.4478863')  
     ,    (9,    'Luis',    'C',    1,    '2022-08-15T08:22:42.4479144')  
         ;  
          
     Alter Table OrderData Add enoughtofulfill varchar(100);  
      
    ;  
     with Q as  
     (  
        SELECT o.enoughtofulfill, sku.QtyAvailable,  
           SUM(TRY_CONVERT(int, o.Quantity)) OVER (PARTITION BY o.SKU ORDER BY o.FileUploaded) AS QtyRequired  
        FROM   OrderData o  
        INNER JOIN SkuData sku ON o.SKU = sku.Sku  
     )  
     update Q   
     set enoughtofulfill = 'no'  
     where QtyRequired > QtyAvailable  
      
    Select * from OrderData;  

233183-screen-shot-2022-08-20-at-14458-pm.png

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 111.8K Reputation points
    2022-08-20T04:50:50.123+00:00

    Try something like this:

    ;  
    with Q as  
    (  
       SELECT o.enoughtofulfill, sku.QtyAvailable,  
          SUM(TRY_CONVERT(int, o.Quantity)) OVER (PARTITION BY o.SKU ORDER BY o.FileUploaded) AS QtyRequired  
       FROM   OrderData o  
       INNER JOIN SkuData sku ON o.SKU = sku.Sku  
    )  
    update Q   
    set enoughtofulfill = 'no'  
    where QtyRequired > QtyAvailable  
    

0 additional answers

Sort by: Most helpful