SQL: Update the column value based on the same column value-CodePudding

I have written this query with the thinking to update the EquipmentMasterId column of #TempRequestItemEquipment temp table from EquipmentMaster table if that EquipmentMasterId is not already been used in the temp table. But it is not working, it is updating the same equipment master id in all the rows of the temp table.

update #TempRequestItemEquipment 
set EquipmentMasterId=
    (
       select top 1 em.Id from EquipmentMaster em
       left join #TempRequestItemEquipment t on em.Id=t.EquipmentMasterId 
       where t.EquipmentMasterId is null
     )

So basically it is not able to detect the changes in the table on the fly while updating it.

How can I achieve this then?

What basically I want is that the distinct EquipmentMasterId's should be populated in my temp table.

It is possible with cursor and loops but I did not want to go that way, so is there even any way to achieve it in one query?

CodePudding user response：

Okay, I have finally come up with the query using with like this and it works. I am using the ROW_NUMBER to connect two tables randomly

;with requestItems as
(
    select ROW_NUMBER() over(order by ri.Id) as RowNumber, ri.* 
    from RequestItem ri
    join Request r on ri.RequestId=r.Id
    where r.RequestNumber=@requestNumber
),
equipmentMasters as
(
    select ROW_NUMBER() over(order by Id) as RowNumber,* 
    from EquipmentMaster
), 
final as 
(
    select 
        em.Id AS EquipmentMasterId,
        ri.Id AS RequestItemId,
        NULL AS ValueDomainId,
        NULL AS ParentEquipmentId,
        1 AS IsRootParent,
        0 AS DeletionIndicator,
        NULL AS BoxID,
        1 AS IsActive
    from requestItems ri
    join equipmentMasters em on ri.RowNumber=em.RowNumber
)
select * from final

CodePudding user response：

It looks like you want this

update t
from #TempRequestItemEquipment t
left join #TempRequestItemEquipment t on em.Id = t.EquipmentMasterId ;