How to make join Table with function cross apply to join by portionkey and portionNumber ?

Question

I work on SQL server 2012 I face issue I can't join cross apply function with join table GlobalPartNumberPortions to

check portionkey and groupid exist on table GlobalPartNumberPortions

here is my statement

 update r set r.Status='Portion Exist Before'
 from #TempImporter r
cross apply
dbo.Split(r.GlobalPartNumber,'$') f where CAST (r.PortionNumber AS INT) = f.Id and r.PortionKey = f.Data

I need to add to statement above another check to portionkey and groupid on table GlobalPartNumberPortions

but I don't know how to join with function

by any way are this syntax below possible and correct or not

inner join  GlobalPartNumberPortions g on t.PortionNumber=g.GroupId  and t.PortionKey=G.PortionKey

join function with table full code as below correct or not

   update r set r.Status='Portion Exist Before'
     from #TempImporter r
    cross apply
    dbo.Split(r.GlobalPartNumber,'$') f where CAST (r.PortionNumber AS INT) = f.Id and r.PortionKey = f.Data
   inner join   GlobalPartNumberPortions g on t.PortionNumber=g.GroupId  and t.PortionKey=G.PortionKey

Answer 1

Check and adjust this syntax:

update r set r.Status='Portion Exist Before'
from #TempImporter r
cross apply dbo.Split(r.GlobalPartNumber,'$') f 
inner join GlobalPartNumberPortions g on t.PortionNumber=g.GroupId  and t.PortionKey=G.PortionKey
where CAST (r.PortionNumber AS INT) = f.Id and r.PortionKey = f.Data

Answer 2

Hi @ahmed salah ,

on t.PortionNumber=g.GroupId and t.PortionKey=g.PortionKey  

whether t is incorrect, it should be r?

I did a test and the following statement seems to work:

update r set r.Status='Portion Exist Before'  
from #TempImporter r  
inner join GlobalPartNumberPortions g   
on r.PortionNumber=g.GroupId and r.PortionKey=g.PortionKey  
cross apply  
dbo.Split(r.GlobalPartNumber,'$') f   
where CAST (r.PortionNumber AS INT) = f.Id and r.PortionKey = f.Data  

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

---

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