SQL Server: populating a table based on another table

Question

I have two tables tblA (Pid int, Lid int, upcoming_Lid int)

(123, 10,null),
(345, 10,null),
(567, 11, null),
(457, 11,null)

tblB (Pid int, Lid int, isActive int)

(123,10,1),
(123,11,0),
(123,12,0),
(123,13,1),
(345,10,1),
(345,11,1),
(567,11,1),
(567,12,1),
(457,11,1),
(457,12,0),
(457,13,1)

Now, I want to populate tblA.upcoming_Lid from tblB where the next lid is active for the same Pid. desired result from tblA:

(123, 10,13),
(345, 10,11),
(567, 11,12),
(457, 11,13) 

Thanks!

Answer 1

This SQL Fiddle demonstrates the following query:

UPDATE tblA
SET upcoming_Lid =
(
  SELECT Lid 
  FROM tblB
  WHERE tblA.Pid = tblB.Pid 
  AND tblB.isActive = 1
  AND tblB.Lid > tblA.Lid 
)