Same Table different Rows, Match values with these 2 columns when not Null

Question

A pretty urgent request came into me and now my brain feels like putty trying to pull this off. can someone point me in the right direction so I can unpack how to do this? I need to replicate values that Warehouse O has in columns “Min_QTY_Allowed” and “ReorderQTY” over to Warehouse I, Unless warehouse O has Null. Then leave the I value as they are. (Image Below) THANK YOU!! [39741-1625205.png][1] [1]: /api/attachments/39741-1625205.png?platform=QnA

Answer 1

If I understand this correctly:

UPDATE I
SET      Min_QTY_Allowed = isnull(O.Min_Qty_Allowed, I.Min_Qty_Allowed),
     ReorderQTY = isnull(O.ReorderQTY I.ReorderQTY)
FROM  tbl I
JOIN   tbl O ON I.item = I.Item
WHERE I.warehouse = 'I'
   AND I.warehouse = 'O'

Note: this is an untested solution. Had you posted the CREATE TABLE statement for your table, and your sample data as INSERT statements, I would have tested my solution.

Answer 2

Hi anonymous user

Please refer to:

    declare @table table(Item char(15),Warehouse char(15),Min_QTY_Allowed int,ReorderQTY int)  
    insert into @table values('ITEM1','Whs_I',NULL,NULL),('ITEM1','Whs_O',2750,4000),  
                             ('ITEM2','Whs_I',NULL,NULL),('ITEM2','Whs_O',100,400),  
      ('ITEM3','Whs_I',10,200),('ITEM3','Whs_O',NULL,NULL)  
    select * from @table  
      
      
    update t1  
    set  Min_QTY_Allowed = isnull(t2.Min_Qty_Allowed, t1.Min_Qty_Allowed),  
         ReorderQTY = isnull(t2.ReorderQTY, t1.ReorderQTY)  
    from @table t1  
    join @table t2   
    on t1.item = t2 .Item and t1.warehouse ='Whs_I' and t2.warehouse ='Whs_O'  
      
    select * from @table  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table