locked
How to update status for part with nothing data when this part is missed? RRS feed

  • Question

  • User696604810 posted

    I work on SQL server 2012 I face issue I can't update status with No data returned

    where no result returned from select statement cross apply function

    meaning where no data returned when join parts and company to function then update status to this

    part with nothing data for that part when data is missing.

    create table #TempPC   
                (  
                        PartNumber NVARCHAR(300),  
                        CompanyId INT,  
                        Status   nvarchar(200)  
                )  
    insert into #TempPC (PartNumber,CompanyId)   
            values  
            ('9C06031A2R43FKHFT',1233345),  
            ('VJ0805AIR5CXAMT',8433324)

    when make select below it not return data for some parts so I will update status with nothing data returned to this part .

    Select t.PartNumber,t.CompanyName,pc.FamilyName,t.Status FROM  #TempPC t    
        
    cross apply  [PC].FN_PartCheck_Test( t.[PartNumber],0,1,1,t.CompanyId) pc     
        
    Where pc.GroupID>-2 And pc.PortionID>-2  

    so what I need to do when any parts and company on temp table join with function is missed

    then update status to this part with nothing data for this part .

    Expected Result

    PartNumber                         CompanyId         status  
    9C06031A2R43FKHFT                  1233345           No data returned  
    VJ0805AIR5CXAMT                    8433324           it have data returned  

    so How to do that Please ?

    select * from [PC].FN_PartCheck_Test( t.[PartNumber],0,1,1,t.CompanyId) pc  
      
    Return ID PartNumber,CompanyID,FamilyID 
    Monday, August 24, 2020 12:49 AM

All replies

  • User-1330468790 posted

    Hi ahmedbarbary,

     

    I suggest you use "Update" operation with "WHERE IN (sub_select)" condition.

    ahmedbarbary

    Select t.PartNumber,t.CompanyName,pc.FamilyName,t.Status FROM  #TempPC t
        
    cross apply  [PC].FN_PartCheck_Test( t.[PartNumber],0,1,1,t.CompanyId) pc
        
    Where pc.GroupID>-2 And pc.PortionID>-2

    I think the above select will give you few records which contain an unique part number for each row. You don't need other columns, e.g. CompanyName, FamilyName since you only need the column "PartNumer" to be the identifier to update the temp table "TempPC".

     

    You could refer to below example:

    PartCheck Table Schema:

    PartCheck Table Data: (Only the first row meets the condition)

    SQL Statement:

    DROP TABLE IF EXISTS #TempPC 
    
    CREATE TABLE #TempPC   
                (  
                        PartNumber NVARCHAR(300),  
                        CompanyId INT,  
                        [Status]   nvarchar(200)  
                )  
    INSERT INTO #TempPC (PartNumber,CompanyId) 
        VALUES ('9C06031A2R43FKHFT',1233345), ('VJ0805AIR5CXAMT',8433324)
    
    
    UPDATE #TempPC 
    SET Status = 'Returned' 
    WHERE PartNumber
    IN ( SELECT t.PartNumber FROM #TempPC t cross apply [dbo].FN_PartCheck_Test(t.[PartNumber],t.CompanyId) pc Where pc.GroupID >-2 And pc.PortionID >-2
    ) Select * FROM #TempPC

    You will get below results:

     

    The key is to use the SQL statement you gave to return a list of the "PartNumber" and then only update the Status with these "PartNumber".

    However, you might have a more complicated scenario that you have to consider more.

    Feel free to let me know if you still have problems. 

     

    Best regards,

    Sean

    Monday, August 24, 2020 4:32 AM