T_SQL Help

Transact-SQL
1

Hi guys Need help.

Here is my sample code

Create table tblsource
(
ID INT
,ADate_Source DATETIME
,lid_update INT
);

CREATE TABLE tbldestination
(
ID INT
,Date_Destination_min DATETIME
,Date_Destination_max DATETIME
,Lid int
);

Insert into tblsource (ID,ADate_Source)
SELECT '1','2017-02-02 18:10:00.000'
UNION
SELECT '2','2017-02-02 18:15:00.000'

insert into tbldestination(ID,Date_Destination_min,Date_Destination_max,Lid)
SELECT '10','2017-02-02 17:50:00.000','2017-02-02 18:20:00.000',428117

update tblsource
SET lid_update = D.Lid
from tblsource s
inner join tbldestination d
ON S.ADate_Source BETWEEN D.Date_Destination_min AND D.Date_Destination_max

SELECT * FROM tblsource
SELECT * FROM tbldestination

drop table tblsource
drop table tbldestination

Here is the result I am getting after I ran above code

ID ADate_Source lid_update
1 2017-02-02 18:10:00.000 428117
2 2017-02-02 18:15:00.000 428117

However, I want this

ID ADate_Source lid_update
1 2017-02-02 18:10:00.000 428117
2 2017-02-02 18:15:00.000

I don't want to use the same ID again. Once I use that ID, I don't want to use again.
Above example are my sample code and the sample scenario.

Thank You for your help in advance.

2

One way is to do something like this ( with the help of CROSS APPLY) :

update tblsource
SET lid_update = CA.lid_update
from tblsource AS s
CROSS APPLY
    (SELECT TOP(1) CA.ID
                 ,D.Lid AS lid_update
     FROM tblsource AS CA
         INNER JOIN  tbldestination AS d
         ON S.ADate_Source BETWEEN D.Date_Destination_min AND D.Date_Destination_max     
     ORDER BY ID ASC
     )CA
WHERE
    CA.ID = S.ID

Output:

	ID 	ADate_Source 		lid_update
	1 	02.02.2017 18:10:00	428117
	2 	02.02.2017 18:15:00 	NULL

http://rextester.com/RQALC42842

3

I belive this works to :

;WITH cte
AS
(
SELECT
    S.id
    ,S.Lid_update    
    ,d.Lid    
    ,ROW_NUMBER () OVER(PARTITION BY d.id ORDER BY s.id ASC) as rn
FROM tblsource s
    inner join tbldestination d 
    ON S.ADate_Source BETWEEN D.Date_Destination_min AND D.Date_Destination_max
)
UPDATE c
SET  
     C.Lid_update = C.Lid
FROM
     cte AS C
WHERE
      Rn = 1

Output for this:

ID	ADate_Source		lid_update
1	02.02.2017 18:10:00	428117
2	02.02.2017 18:15:00	NULL