کمک در مورد دستور Update

[mersad00]

سلام من یه مشکل بزرگ با دستور آپدیت دارم، از این قرار که می خوام جدولی رو آپدیت کنم که هر ردیف از یکسری محاسبات روی ردیف های ماقبلش بدست میاد، مشکل اینجاست که در طول آپدیت سرور متوجه تغییرات ماقبل نمیشه و اونارو با همون مقدار قبل از اجرای آپدیت در نظر می گیره و محاسبه می کنه! برای وضوح بیشتر نمونه جدول رو ایجاد:


create table trans(
id bigint identity(1,1) not null,
price decimal(18,0) null,
relatedid bigint null)


حالا این مقادیر رو اینزت کنید:

Insert into trans (price,relatedid)values(100,NULL)
Insert into trans (price,relatedid)values(500,NULL)
Insert into trans (price,relatedid)values(100,NULL)
Insert into trans (price,relatedid)values(1,2)
Insert into trans (price,relatedid)values(200,NULL)


حالا اینو اجرا کنید

UPDATE trans
SET price = CASE WHEN relatedID IS NOT NULL
THEN
(select avg(price) from trans where id<=t.relatedid)
ELSE
(select avg(price) from trans where id<=t.id)
END
FROM trans t


انتظار میره نتیجه این باشه:

id-----price-----relatedid
1------100------NULL
2------300------NULL
3------166.6------NULL
4------300--------2
5------213.32------NULL


در حالی که جواب این نیست، لطفا اگه راه حلی چیزی به ذهنتون می رسه کمک کنید!

[m_omrani]

فکر کنم علتش این باشه که در زمان هایی که relatedid مقدار NULL داره، وقتی case شما وارد else می شه، select avg(price) شما به خاطر شرط where id<t.id مقداری بر نمی گردونه و در واقع null بر می گردونه. علتش هم اینه که مقایسه مقادیر با null نتیجه اش null هست. این رو با کُد زیر می تونین تست کنین:

select case when 5 < null then 'yes' else 'no' end

بعد از اجرای این دستور مقدار 'no' نمایش داده می شه.

[m_omrani]

شاید هم علتش استفاده از => به جای > در قسمت where پرس و جوی select avg(price) در مواقع غیر NULL بودن relatedid باشه.

[Reza_Yarahmadi]

این اتفاق به این دلیل میفته که روند اسکن کردن رکوردها بر روی داده های قبلی انجام میشه و تغییرات در Transaction File ذخیره میشه. وقتی همه رکوردها تغییر پیدا کردند اطلاعات این فایل بر روی فایل اصلی قرار میگیره.
یکی از راههایی که میتونید کارتون رو راه بندازید بصورت زیره
Declare _Cursor Cursor For Select * From Trans
Declare @ID Bigint,
@Price Decimal(18, 0),
@RelatedId Bigint
Open _Cursor
Fetch From _Cursor Into @ID, @Price, @RelatedId
While(@@Fetch_Status = 0)
Begin
Update Trans Set
Price = Case
When @relatedID IS NOT NULL Then
(Select AVG(Price) From Trans Where ID <= @RelatedId)
Else
(Select AVG(Price) From Trans Where ID <= @ID)
End
Where ID = @ID

Fetch From _Cursor Into @ID, @Price, @RelatedId
End

[محمد سلیم آبادی]

این اتفاق به این دلیل میفته که روند اسکن کردن رکوردها بر روی داده های قبلی انجام میشه و تغییرات در Transaction File ذخیره میشه. وقتی همه رکوردها تغییر پیدا کردند اطلاعات این فایل بر روی فایل اصلی قرار میگیره.
یکی از راههایی که میتونید کارتون رو راه بندازید بصورت زیره
Declare _Cursor Cursor For Select * From Trans
Declare @ID Bigint,
@Price Decimal(18, 0),
@RelatedId Bigint
Open _Cursor
Fetch From _Cursor Into @ID, @Price, @RelatedId
While(@@Fetch_Status = 0)
Begin
Update Trans Set
Price = Case
When @relatedID IS NOT NULL Then
(Select AVG(Price) From Trans Where ID <= @RelatedId)
Else
(Select AVG(Price) From Trans Where ID <= @ID)
End
Where ID = @ID

Fetch From _Cursor Into @ID, @Price, @RelatedId
End

پیشنهاد می کنم آخرین سطر بعد از بروز رسانی رو ببینین. روشتون خروجی و نتیجه مورد نظر رو تولید نمیکنه.

[محمد سلیم آبادی]

با این فرض که از نسخه 2005 به بعد استفاده می کنید،
ابتدا بایستی یک سری تغییرات روی ساختار جدولی که ارسال کردین بدیم.
1. جدولتون کلید اصلی نداره
2. جدولتون قید چک برای بررسی مقدار relatedid نداره
3. نوع داده Decimal دقیق در نظر گرفته نشده

create table trans(
id bigint identity(1,1) not null primary key,
price decimal(18,2) null,
relatedid bigint null,
check (relatedid <= id));

Insert into trans (price,relatedid)values(100,NULL)
Insert into trans (price,relatedid)values(500,NULL)
Insert into trans (price,relatedid)values(100,NULL)
Insert into trans (price,relatedid)values(1,2)
Insert into trans (price,relatedid)values(200,NULL)

برای حل این مساله از روش پیشنهادیم نیاز هست به یک جدول اعداد و یک تابع که برای ایجادشتون یکبار برای همیشه این کدها رو اجرا کنید (من تعداد سطرهای جدولتون رو یکصد هزارتا تخمین زدم اگر بیش از این سطر در جدول موجود هست این عدد را افزایش دهید):


CREATE FUNCTION dbo.Numbers (@N INT) RETURNS TABLE AS
RETURN (WITH RecCTE (nbr) AS
(SELECT 1
UNION ALL
SELECT nbr + 1 FROM RecCTE WHERE nbr < 100),

Nums (nbr) AS
(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1))
FROM RecCTE AS C1
CROSS APPLY
RecCTE AS C2
CROSS APPLY
RecCTE AS C3)
SELECT nbr
FROM Nums
WHERE nbr <= @N);
GO

--Creating Number Table using TVF
SELECT n.nbr
INTO Nums
FROM dbo.Numbers(100000) AS n;

CREATE FUNCTION dbo.splitter (@S VARCHAR(MAX), @D CHAR(1)) RETURNS TABLE AS
RETURN (SELECT cast(CASE WHEN CHARINDEX(@D, @S + @D, nbr) - nbr = 0 THEN ''
ELSE SUBSTRING(@S, nbr, CHARINDEX(@D, @S + @D, nbr) - nbr)
END as decimal(18,2)) AS Word, nbr
FROM Nums
WHERE nbr <= LEN(@S)
AND SUBSTRING(@D + @S, nbr, 1) =@D);
GO



و بعد از ساخت اینها از کوئری زیر برای Update استفاده کنید:


--Befor Update:
SELECT * FROM trans


;WITH cteSource
AS (
SELECT ID,
price,
price AS Aggregated,
price AS Accumulated,
CAST(price AS VARCHAR(100)) AS sum_,
relatedid
FROM trans
WHERE ID = 1

UNION ALL

SELECT t.ID,
t.price,
CASE WHEN t.relatedid IS NULL THEN CAST((s.Accumulated + t.price) / t.ID AS decimal(18,2))
ELSE CAST((SELECT word
FROM (SELECT word, ROW_NUMBER() OVER(ORDER BY nbr) AS rnk
FROM dbo.splitter(s.sum_,',')
)D
WHERE rnk = t.relatedid) AS decimal(18,2))
END AS Aggregated,
CASE WHEN t.relatedid IS NULL THEN CAST(s.Accumulated + (s.Accumulated + t.price) / t.ID AS decimal(18,2))
ELSE CAST(s.Accumulated + CAST((SELECT word
FROM (SELECT word, ROW_NUMBER() OVER(ORDER BY nbr) AS rnk
FROM dbo.splitter(s.sum_,',')
)D
WHERE rnk = t.relatedid) AS decimal(18,2)) AS decimal(18,2))
END AS Accumulated,
CAST(s.sum_ + ',' + CAST((t.price + s.price)/t.id AS VARCHAR(100)) AS VARCHAR(100)),
t.relatedid
FROM cteSource AS s
INNER JOIN trans AS t ON t.ID = s.ID + 1
)
UPDATE T
SET price = aggregated
FROM trans AS T
JOIN cteSource AS C
ON T.id = C.id


--After Update:
SELECT * FROM trans

/*--Befor Update:
id price relatedid
-------------------- --------------------------------------- --------------------
1 100.00 NULL
2 500.00 NULL
3 100.00 NULL
4 1.00 2
5 200.00 NULL


--After Update:
id price relatedid
-------------------- --------------------------------------- --------------------
1 100.00 NULL
2 300.00 NULL
3 166.67 NULL
4 300.00 2
5 213.33 NULL
*/

مطمئنا این کوئری از لحاظ کد بهینه تر هم بایستی بشود ولی از آنجایی که با خستگی به سوالتان پاسخ دادم تنها به نتیجه گرفتن فکر می کردم.