[SOLVED] Using multiple data from a SQL UPDATE query

Using multiple data from a SQL UPDATE query

I am writing data to a temporary table using the query below. For this I use the update command. However, some days the values in the table may be more than one, in which case my update query selects the first value. How do I get him to select the largest one when multiple values come? I used the MAX command and CROSS JOIN methods, but I got the wrong values. personel_giriscikis table

declare @kartno nvarchar(50);
declare @ilktarih datetime;
declare @sontarih datetime;
declare @devamsizliktablo TABLE(
Tarih datetime,
Giris time,
Cikis time,
Gun nvarchar(50),
Durumu nvarchar(50),
Hesap float,
Vardiya nvarchar(50),
Vardiyagsaat time,
Vardiyacsaat time,
Gecgelme float,
Erkencikma float)

UPDATE @devamsizliktablo SET Giris=p.cikis_saat 
FROM dbo.personel_giriscikis AS p 
LEFT OUTER JOIN dbo.personel_kartlari AS pk ON pk.id=p.personel_id 
LEFT OUTER JOIN dbo.Takvim AS t ON t.tarih=p.tarih
LEFT OUTER JOIN @devamsizliktablo AS d ON d.Tarih=p.tarih
WHERE p.tarih>=@ilktarih AND p.tarih<=@sontarih AND pk.kartno=@kartno AND d.Tarih=p.tarih

Solution

First of all, you don't need to use LEFT JOIN. You can use GROUP BY to find the max value and then do the update:

;WITH cte_max(Tarih,cikis_saat)
AS
(
  SELECT d.Tarih, MAX(p.cikis_saat) AS cikis_saat
  FROM dbo.personel_giriscikis AS p 
  INNER JOIN dbo.personel_kartlari AS pk ON pk.id=p.personel_id 
  INNER JOIN dbo.Takvim AS t ON t.tarih=p.tarih
  INNER JOIN @devamsizliktablo AS d ON d.Tarih=p.tarih
  WHERE p.tarih>=@ilktarih AND p.tarih<=@sontarih AND pk.kartno=@kartno
  GROUP BY d.Tarih 
)
UPDATE d
SET d.Giris=m.cikis_saat 
FROM @devamsizliktablo d
INNER JOIN cte_max m
ON m.Tarih=d.Tarih;