Update a column using the same column from another table and link to another and make the sum equal
There are 2 tables - DepoSevkDetay
and SatisDetay
. Both have columns - Miktar
and UrunID
. I need to make an amount Miktar
in DepoSevkDetay
for each UrunID
equal to the amount Miktar
in SatisDetay
. The request I wrote did not handle this. What am I doing wrong? Considering that the number of records in the two tables is not equal
UPDATE
Table_A
SET
Table_A.[Miktar] = (((Table_B.Miktar)/Table_C.c) +
(case when ((Table_B.Miktar) % Table_C.c < Table_C.r) then 1
else 0 end ))
FROM
[Retail].[dbo].[tb_DepoSevkDetay] AS Table_A
INNER JOIN (
SELECT SUM(Miktar)as Miktar, UrunID
FROM tb_SatisDetay
GROUP BY UrunID ) AS Table_B
ON Table_A.[UrunID] = Table_B.[UrunID]
INNER JOIN (
SELECT COUNT([UrunID]) AS c, ROW_NUMBER() OVER (ORDER BY UrunID) r, UrunID as UrunID
FROM tb_DepoSevkDetay
GROUP BY UrunID ) AS Table_C
ON Table_B.[UrunID] = Table_C.[UrunID]
source to share
I would do it like this. First, I would do Miktar = 0:
UPDATE [Retail].[dbo].[tb_DepoSevkDetay]
SET [Miktar] = 0
Then I would update all entries, but first with Miktar equal to the sum of (Miktar) / (Count (*))
; with Table_A as (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY UrunID ORDER BY UrunID) as rn,
COUNT(*) OVER (PARTITION BY UrunID) as c
[Retail].[dbo].[tb_DepoSevkDetay])
UPDATE
Table_A
SET
Table_A.[Miktar] = Table_B.Miktar/(Table_A.c)
FROM
Table_A
INNER JOIN (
SELECT SUM(Miktar)as Miktar, UrunID
FROM tb_SatisDetay
GROUP BY UrunID ) AS Table_B
ON Table_A.[UrunID] = Table_B.[UrunID]
WHERE Table_A.c > 0 and Table_A.rn > 1
And then I updated the remaining [Miktar], for example:
UPDATE
Table_A
SET
Table_A.[Miktar] = Table_B.Miktar - Table_C.Miktar
FROM
[Retail].[dbo].[tb_DepoSevkDetay] AS Table_A
INNER JOIN (
SELECT SUM(Miktar)as Miktar, UrunID
FROM tb_SatisDetay
GROUP BY UrunID ) AS Table_B
ON Table_A.[UrunID] = Table_B.[UrunID]
INNER JOIN (
SELECT SUM(Miktar)as Miktar, UrunID
FROM [Retail].[dbo].[tb_DepoSevkDetay]
GROUP BY UrunID ) AS Table_C
ON Table_A.[UrunID] = Table_C.[UrunID]
WHERE Table_A.[Miktar] = 0
Hope this makes sense
source to share