از پایگاه داده ها برای ذخیره اطلاعات در جدول ها استفاده می کنیم. در بیشتر مواقع داده های جدول در برخی از فیلدها نیاز به روزرسانی دارد. در زبان SQL از دستور UPDATE برای به روزرسانی استفاده می کنیم. در این آموزش دستور UPDATE را به طور کامل همراه با مثال های مختلف پوشش می دهیم.
ساختار UPDATE ساده در SQL
فرم کلی دستور UPDATE ساده:
توی این پک فوق العاده، My Sql رو جوری یاد میگیری که تو هیچ کلاس آموزشی یا پک دیگه ای نه دیدی نه شنیدی! همراه با فایل های تمرینی، پروژه محور یاد بگیر و حسابی پول در بیار!
UPDATE Table
SET Column = <some expression>;
به روزرسانی یک ستون
در مثال زیر فرض کنید[SickLeaveHours] نام ستون با نوع داده INT در جدول [HumanResources].[Employee] است. [SickLeaveHours] روی 0 تنظیم شده است که همان عبارت به روزرسانی رکوردهای موجود است:
UPDATE [HumanResources].[Employee]
SET [SickLeaveHours] = 0;
با اجرا این دستور، مقدار همه ردیف های داده موجود در ستون SickLeaveHours در جدول Employee برابر با 0 می شود. همچنین می توانید از عبارت محاسباتی با ارجاع به همان ستون استفاده کنید. در مثال زیر مقدار ستون VacationHours 10 افزایش یافته اسن.
UPDATE [HumanResources].[Employee]
SET [VacationHours] = [VacationHours] * 1.1;
با اجرای دستور UPDATE، SQL Server مقدار فعلی هر سطر را در عدد 1.1 ضرب کرده و نتیجه را در همان جا ذخیره می کند.
حتما دانلود کنید: آموزش پایگاه داده از صفر تا صد با 23 درس رایگان+ جزوه PDF
به روزرسانی چند ستون
می توانید چند ستون را همزمان به روزرسانی کنید، ستون ها با کاما از یکدیگر جدا می شوند. در عبارت زیر، ستون ModifiedDate را نیز در جدول به روزرسانی می کنیم:
UPDATE [HumanResources].[Employee]
SET [VacationHours] = [VacationHours] * 1.1
,[ModifiedDate] = GETDATE();
ممکنه برای به روزرسانی یک ستون از ستون های دیگر همان جدول استفاده شود. در مثال زیر برای به روزرسانی ستون SickLeaveHours از ستون VacationHours کرده ایم.
UPDATE [HumanResources].[Employee]
SET [VacationHours] = 0
,[SickLeaveHours] = IIF([VacationHours] < 10, 0, 25);
اگر SQL Server ابتدا ستون VacationHours را به روزرسانی می کرد، بنابراین براساس نتیجه عبارت IIF احتمالا ستون SickLeaveHours نیز صفر می شد. اما اینجوری نیست، موتور پایگاه داده هر دو ستون را به طور همزمان به روزرسانی می کند، بنابراین عبارت IIF از مقادیر ستون VacationHours قبل از دستور UPDATE استفاده می کند.
به روزرسانی زیرمجموعه ای از ردیف ها
در بسیاری از موارد نمی خواهید همه ردیف های جدول را به روزرسانی کنید فقط تعدادی از ردیف ها. با اضافه کردن عبارت WHERE به دستور UPDATE می توانید این کار را انجام دهید.
UPDATE myTable
SET myColumn = <some expression>
WHERE <Boolean expressions>;
هر ردیف از جدول که شرط WHERE برای آن برقرار باشد، به روزرسانی خواهد شد و سایر ردیف ها نادیده گرفته می شوند. همان شرط WHERE در دستور SELECT است. به عنوان مثال می خواهیم ردیف های ستون SickLeaveHours با مقدار کوچکتر از 10 را به روزرسانی کنیم:
UPDATE [HumanResources].[Employee]
SET [SickLeaveHours] = 0
WHERE [SickLeaveHours] < 10;
به طور کلی از نظر عملکرد این بهترین روش برای محدود کردن ردیف های مورد نظر برای به روز رسانی است و همچنین جلوگیری از رونویسی داده هایی که نباید به روزرسانی شوند.
به روزرسانی یک جدول با داده های چند جدول
در بخش قبلی برای به روزرسانی مقادیر ستون از مقادیر مستقیم و عبارات شامل خود ستون های جدول استفاده کردیم. می توانید از داده های جدول های دیگر نیز استفاده کنید. برای این کار FROM را به دستور UPDATE اضافه می کنیم و با JOIN جدول ها را به یکدیگر پیوند می دهیم سپس می توانیم از ستون های سایر جدول ها برای به روزرسانی جدول فعلی استفاده کنیم.
فرم کلی کوئری می تواند به صورت زیر باشد:
UPDATE t1
SET myColumn = <some expression using columns from t1 and t2>
FROM myTable t1
JOIN otherTable t2 ON t1.keycolumn = t2.keycolumnWHERE <Boolean expressions>;
اولین جدول در FROM جدول مورد نظر برای به روزرسانی است (t1 درشبه کد بالا). سپس آن را به یک جدول دیگر پیوند می دهیم (جدول t2). t1 و t2 نام های مستعار هستند و myTable و otherTable به ترتیب نام اصلی جدول اصلی و جدول دوم است.
نکته: توجه داشته باشید که بعد از کلمه کلیدی UPDATE به جای استفاده از نام اصلی جدول از نام مستعار t1 استفاده می شود تا جدول مورد نظر را برای به روزسانی مشخص کند.
در مثال زیر مقدار VacationHours را برای نمونه های جدول JobCandidate در جدول Employee دو واحد افزایش می دهیم بنابراین باید جدول VacationHours به جدول JobCandidate پیوند داده شود.
(در واقع در این مثال اطلاعات کارمندان تازه کار و بدون قرارداد در جدول JobCandidate قرار دارد و می خواهیم به ساعت های تعطیل آنها که در ستون VacationHours در جدول کارمندان VacationHours قرار دارد، فقط برای این کارمندان دو ساعت اضافه کنیم.)
UPDATE e
SET [VacationHours] = e.[VacationHours] + 2
FROM [HumanResources].[Employee] e
INNER JOIN [HumanResources].[JobCandidate] jc ON [jc].[BusinessEntityID] = [e].[BusinessEntityID];
استفاده از WHERE لازم نیست زیرا INNER JOIN داده های مورد نیاز را فیلتر می کند.
در منطق های پیچیده تر ممکنه نیاز به چند کوئری یا CTE داشته باشید. CTE در ایجاد ماژول های کوچک با امکان استفاده مجدد کمک می کند و کوئری های پیچیده را خواناتر خواهد کرد.
کوئری زیر یک خطا برمی گرداند زیرا نمی توانید از توابع تجمیعی (aggregate) به طور مستقیم در SET دستور UPDATE استفاده کنید.
UPDATE sh
SET [SubTotal] = SUM(sd.[UnitPrice] * sd.[OrderQty])
FROM [Sales].[SalesOrderHeader] sh
JOIN [Sales].[SalesOrderDetail] sd ON [sd].[SalesOrderID] = [sh].[SalesOrderID]
WHERE sh.[SalesOrderID] = 43659;
برای حل این مسئله می توانید از یک CTE برای پیش محاسبات استفاده کنید:
WITH CTE_agg AS
(
SELECT [SalesOrderID], Subtotal = SUM([UnitPrice] * [OrderQty])
FROM [Sales].[SalesOrderDetail]
WHERE [SalesOrderID] = 43659
GROUP BY [SalesOrderID]
)
UPDATE sh
SET [SubTotal] = c.[Subtotal]
FROM [Sales].[SalesOrderHeader] sh
JOIN CTE_agg c ON c.[SalesOrderID] = [sh].[SalesOrderID];
تست دستور UPDATE
از آنجا که استفاده از دستور UPDATE منجر به رونویسی داده ها می شود باید مطمئن شوید که دستور UPDATE همان کار مورد نظرتان را انجام می دهد. برای تست سریع دو گزینه وجود دارد:
- استفاده از Transaction
- وارد کردن SELECT
استفاده از Transaction
Transaction به معنای تراکنش و مجموعه ای از چند دستور است که برای اطمینان از موفقیت یا عدم موفقیت یک عملیات انجام می شود.
در این روش با تراکنش های صریح کوئری UPDATE را بررسی می کنید:
BEGIN TRAN;
UPDATE Table
SET Column = <some expression>
WHERE <Boolean expressions>;
COMMIT;
ROLLBACK;
دستور BEGIN TRAN همراه با دستور UPDATE اجرا شده است. این، داده های جدول را داخل فضای تراکنش فعلی به روزرسانی می کند. می توانید داده های جدول را بررسی کنید تا مطمئن شوید همه موارد به درستی به روزرسانی شده اند. در این مورد می توانید دستور COMMIT را برای انجام تراکنش اجرا کنید یا از دستور ROLLBACK برای برگرداندن تراکنش و تغییرات در جدول استفاده کنید.
با استفاده از کوئری قبلی اسکریپت زیر را می نویسیم:
BEGIN TRAN;
WITH CTE_agg AS
(
SELECT [SalesOrderID], Subtotal = SUM([UnitPrice] * [OrderQty])
FROM [Sales].[SalesOrderDetail]
WHERE [SalesOrderID] = 43659
GROUP BY [SalesOrderID]
)
UPDATE sh
SET [SubTotal] = c.[Subtotal]
FROM [Sales].[SalesOrderHeader] sh
JOIN CTE_agg c ON c.[SalesOrderID] = [sh].[SalesOrderID];
SELECT [SubTotal]
FROM [Sales].[SalesOrderHeader]
WHERE[SalesOrderID] = 43659;
ROLLBACK;
COMMIT;
برای تست ابتدا این قسمت را اجرا می کنیم:
اگر نتیجه همان چیزی باشد که انتظار داریم، دستور COMMIT می تواند اجرا شود در غیر این صورت ROLLBACK را انتخاب می کنیم. تا زمانی که هیچ کدام از این دستورات اجرا نشود، تراکنش باز است و تغییرات روی داده ها حفظ و ذخیره نمی شود.
استفاده از دستور Select
گزینه دیگر استفاده از SELECT در دستور UPDATE است و آن را با استفاده از دستور کامنت “پنهان” کنید. برای تست کوئری فقط قسمت مربوطه را انتخاب می کنیم تا تغییرات داده های موردنظر را ببینیم. برای مثال:
UPDATE e
SET [VacationHours] = e.[VacationHours] + 2
— SELECT *
FROM [HumanResources].[Employee] e
JOIN [HumanResources].[JobCandidate] jc ON [jc].[BusinessEntityID] = [e].[BusinessEntityID];
برای تست این کوئری همه قسمت های بعد از دو خط تیره را انتخاب کرده و آن را اجرا می کنیم:
با این ترفند به شرطی که دستور UPDATE شامل قسمت FROM باشد، می توانید به سرعت بررسی کنید که کدام ردیف ها به روزرسانی می شوند.
در کنار این مطلب حتما دانلود کنید: دانلود رایگان آموزش کامل sql (فیلم+جزوه pdf)
به روزرسانی View
در همه مثال ها از جدول های فیزیکی برای به روزرسانی داده های استفاده کردیم. اما SQL Server امکان به روزرسانی داده ها از طریق View را نیز فراهم کرده است. دستور UPDATE فقط می تواند به ستون های یک جدول پایه ارجاع دهد. یعنی اینکه به روزرسانی همزمان چند جدول با استفاده از یک دستور UPDATE امکان پذیر نیست اما با View می توانید این کار را انجام دهید.
شرط های لازم برای یک View قابل به روزرسانی:
- ستون های view که اصلاح می شوند باید به طور مستقیم به داده های جدول پایه ارجاع دهند. یعنی اینکه نمی توان از یک تابع تجمیعی یا هر عبارتی با استفاده از ستون های دیگر استفاده کرد. استفاده از (UNION (ALL یا سایر عملگرهای مجموعه نیز ممنوع است.
- هیچ کدام از ORDER BY، GROUP BY، HAVING یا DISTINCT وجود ندارد.
- TOP همراه با بند WITH CHECK OPTION استفاده نمی شود.
در مثال زیر view بر اساس یک کوئری SELECT است:
CREATE VIEW [HumanResources].[vEmployee] AS
SELECT
e.[BusinessEntityID]
,p.[Title]
,p.[FirstName]
,p.[MiddleName]
,p.[LastName]
,p.[Suffix]
,e.[JobTitle]
,pp.[PhoneNumber]
,pnt.[Name] AS [PhoneNumberType]
,ea.[EmailAddress]
,p.[EmailPromotion]
,a.[AddressLine1]
,a.[AddressLine2]
,a.[City]
,sp.[Name] AS [StateProvinceName]
,a.[PostalCode]
,cr.[Name] AS [CountryRegionName]
,p.[AdditionalContactInfo]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]
INNER JOIN [Person].[BusinessEntityAddress] bea
ON bea.[BusinessEntityID] = e.[BusinessEntityID]
INNER JOIN [Person].[Address] a
ON a.[AddressID] = bea.[AddressID]
INNER JOIN [Person].[StateProvince] sp
ON sp.[StateProvinceID] = a.[StateProvinceID]
INNER JOIN [Person].[CountryRegion] cr
ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
LEFT OUTER JOIN [Person].[PersonPhone] pp
ON pp.BusinessEntityID = p.[BusinessEntityID]
LEFT OUTER JOIN [Person].[PhoneNumberType] pnt
ON pp.[PhoneNumberTypeID] = pnt.[PhoneNumberTypeID]
LEFT OUTER JOIN [Person].[EmailAddress] ea
ON p.[BusinessEntityID] = ea.[BusinessEntityID];
همانطور که مشاهده می کنید در view همه شرط ها رعایت شده است. این بدان معنی است که می توانیم عبارت UPDATE زیر را اجرا کنیم:
UPDATE [HumanResources].[vEmployee]
SET [Title] = ‘Testing’
WHERE [BusinessEntityID] = 255;
می ببینیم که داده ها تغییر کرده اند:
view چند جدول دارد که با استفاده از INNER JOINS به هم متصل شده اند یعنی اینکه می توانید ستون های جدول های دیگر مانند جدول Person.Address را به روزرسانی کنید.
نکات و ترفندهای UPDATE
- اگر می خواهید چند جدول را به طور همزمان به روزرسانی کنید با یک دستور UPDATE امکان پذیر نیست.
- برای تست باید باید از transaction های صریح استفاده کنید.
- می توانید ردیف ها را به طور همزمان با استفاده از دستور MERGE به روز رسانی، حذف و درج کنید.
- اجرای یک دستور UPDATE طولانی می تواند یک فرآیند پرهزینه باشد. SQL Server علاوه بر به روزرسانی داده ها باید قفل های روی جدول را حفظ کرده و گزارش تراکنش را به روزرسانی می کند.
- می توانید با استفاده ازrowcount@ به صورت برنامه ریزی شده تعیین کنید که چند ردیف را به روزرسانی کرده اید.
- می توانید از دستور UPDATE در هر جایی که اسکریپت های SQL قابل اجرا هستند، مثل SQL Server Management Studio، stored procedures، Integration Services، sqlcmd، PowerShell، Azure Data Factory، SQL Server Agent و … استفاده کنید.
