آموزش جامع SQL Server (جلسه ۱۲)
1398/04/30 17:42 , میلاد صاحب نظر

آموزش جامع SQL Server (جلسه ۱۲)

دستور UPDATE در SQL Server

برای تغییر داده‌های موجود در یک جدول، از دستور UPDATE به شکل زیر استفاده می‌کنیم:

UPDATE table_name
SET c1 = v1, c2 = v2, ... cn = vn
[WHERE condition]

در این syntax:

  • ابتدا، نام جدولی که قرار است داده‌های آن به روز رسانی شوند مشخص شده است.
  • دوم، لیستی از ستون‌های c1, c2, …, cn و مقادیر v1, v2, …, vn که باید به روز رسانی شوند مشخص شده است.
  • سوم، شروط مجود در دستور WHERE، ردیف‌هایی که باید به روز رسانی شوند را انتخاب می‌کنند.
    دستور WHERE اجباری نیست. اگر از دستور WHERE استفاده نکنید، تمام ردیف‌های جدول به روز رسانی می‌شوند.

مثال‌های دستور UPDATE در SQL Server

ابتدا، اجازه دهید یک جدول جدید به نام taxes برای مثال‌ها ایجاد کنیم.

CREATE TABLE sales.taxes (
 tax_id INT PRIMARY KEY IDENTITY (1, 1),
 state VARCHAR (50) NOT NULL UNIQUE,
 state_tax_rate DEC (3, 2),
 avg_local_tax_rate DEC (3, 2),
 combined_rate AS state_tax_rate + avg_local_tax_rate,
 max_local_tax_rate DEC (3, 2),
 updated_at datetime
);

سپس، کد زیر را برای وارد کردن داده‌ها به درون جدول taxes اجرا کنید:

INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('Alabama',0.04,0.05,0.07);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('Alaska',0,0.01,0.07);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('Arizona',0.05,0.02,0.05);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('Arkansas',0.06,0.02,0.05);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('California',0.07,0.01,0.02);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('Colorado',0.02,0.04,0.08);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('Connecticut',0.06,0,0);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('Delaware',0,0,0);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('Florida',0.06,0,0.02);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('Georgia',0.04,0.03,0.04);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('Hawaii',0.04,0,0);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('Idaho',0.06,0,0.03);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('Illinois',0.06,0.02,0.04);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('Indiana',0.07,0,0);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('Iowa',0.06,0,0.01);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('Kansas',0.06,0.02,0.04);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('Kentucky',0.06,0,0);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('Louisiana',0.05,0.04,0.07);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('Maine',0.05,0,0);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('Maryland',0.06,0,0);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('Massachusetts',0.06,0,0);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('Michigan',0.06,0,0);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('Minnesota',0.06,0,0.01);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('Mississippi',0.07,0,0.01);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('Missouri',0.04,0.03,0.05);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('Montana',0,0,0);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('Nebraska',0.05,0.01,0.02);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('Nevada',0.06,0.01,0.01);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('New Hampshire',0,0,0);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('New Jersey',0.06,0,0);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('New Mexico',0.05,0.02,0.03);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('New York',0.04,0.04,0.04);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('North Carolina',0.04,0.02,0.02);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('North Dakota',0.05,0.01,0.03);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('Ohio',0.05,0.01,0.02);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('Oklahoma',0.04,0.04,0.06);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('Oregon',0,0,0);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('Pennsylvania',0.06,0,0.02);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('Rhode Island',0.07,0,0);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('South Carolina',0.06,0.01,0.02);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('South Dakota',0.04,0.01,0.04);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('Tennessee',0.07,0.02,0.02);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('Texas',0.06,0.01,0.02);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('Utah',0.05,0,0.02);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('Vermont',0.06,0,0.01);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('Virginia',0.05,0,0);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('Washington',0.06,0.02,0.03);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('West Virginia',0.06,0,0.01);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('Wisconsin',0.05,0,0.01);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('Wyoming',0.04,0.01,0.02);
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES('D.C.',0.05,0,0);
۱)مثال به روز رسانی یک ستون در تمام ردیف‌ها

کد زیر یک ستون را برای تمام ردیف‌های جدول Taxes به روز رسانی می‌کند:

UPDATE sales.taxes
SET updated_at = GETDATE();

در این مثال، مقادیر موجود در ستون updated_at به date time سیستمی بازگردانده شده توسط تابع ()GETDATE تغییر کرده‌اند.

برای کد بالا SQL Server پیغام زیر را نشان می‌دهد:

(51 rows affected)

این بدان معنا است که ۵۱ ردیف با موفقیت به روز رسانی شده‌اند.

برای تائید درستی به روز رسانی از query زیر استفاده کنید:

SELECT
    *
FROM
    sales.taxes;

مشخصاً در کد بالا، مقادیر ستون updated_at با مقادیر تاریخ فعلی به روز رسانی می‌شوند.

۲)مثال به روز رسانی چندین ستون

کد زیر حداکثر نرخ مالیات محلی را تا ۲ درصد و متوسط نرخ مالیات محلی را تا ۱ درصد برای ایالت‌هایی که دارای نرخ مالیات محلی حداکثر ۱ درصد هستند، افزایش می‌دهد.

UPDATE sales.taxes
SET max_local_tax_rate += 0.02,
    avg_local_tax_rate += 0.01
WHERE
    max_local_tax_rate = 0.01;

پیغامی که توسط SQL Server بازگردانده می‌شود این است:

(7 rows affected)

این بدان معنا است که مالیات‌های ۷ ایالت به روز رسانی شده است.

به این ترتیب، در این بخش چگونگی استفاده از دستور UPDATE در SQL Server برای تغییر داده‌های موجود در یک جدول را آموختید.

دستور UPDATE JOIN در SQL Server

برای به دست آوردن داده‌ها از جداول مرتبط، گاهی می‌توان از دستور‌های join (یعنی inner join‌ یا left join) استفاده کرد. در SQL Server، می‌توانید از این دستورهای join درون دستور UPDATE استفاده کنید تا یک به روز رسانی بین جدولی انجام دهید.

کد زیر syntax دستور UPDATE JOIN را نشان می‌دهد:

UPDATE 
    t1
SET 
    t1.c1 = t2.c2,
    t2.c2 = expression,
    ...   
FROM 
    t1
    [INNER | LEFT] JOIN t2 ON join_predicate
WHERE 
    where_predicate;

در این syntax:

  • ابتدا، نام جدول (t1) که می‌خواهید به روز رسانی شود را در دستور UPDATE می‌نویسید.
  • سپس، مقدار جدید برای هر ستون از جدولی که قرار است به روز رسانی شده را مشخص می‌کنید.
  • بعد، دوباره جدولی که می‌خواهید به روز رسانی شود را در دستور FROM مشخص می‌کنید.
  • پس از آن، از یکی از دستورهای INNER JOIN یا LEFT JOIN برای اتصال یک جدول دیگر (t2) با استفاده از یک شرط اتصال که پس از کلیدواژه ON قرار می‌گیرد، استفاده می‌کنید.
  • در آخر، یک دستور WHERE برای مشخص کردن ردیف‌هایی که باید به روز رسانی شوند اضافه می‌کنید که این بخش اجباری نیست.

مثال‌های UPDATE JOIN در SQL Server

اجازه دهید چند مثال برای استفاده از دستور UPDATE JOIN را بررسی کنیم.

آماده‌سازی جداول نمونه

ابتدا، یک جدول جدید به نام sales.targets برای ذخیره اهداف فروش ایجاد می‌کنیم:

DROP TABLE IF EXISTS sales.targets;
 
CREATE TABLE sales.targets
(
    target_id  INT PRIMARY KEY, 
    percentage DECIMAL(4, 2) 
        NOT NULL DEFAULT 0
);
 
INSERT INTO 
    sales.targets(target_id, percentage)
VALUES
    (1,0.2),
    (2,0.3),
    (3,0.5),
    (4,0.6),
    (5,0.8);

اگر کارکنان بخش فروش به هدف ۱ برسند، نسبت کمیسیون فروش ۰.۲ یا ۲۰ درصد را به دست خواهند آورد و الی آخر.

سپس، یک جدول دیگر به نام sales.commissions برای ذخیره کمیسیون‌های فروش ایجاد می‌کنیم:

CREATE TABLE sales.commissions
(
    staff_id    INT PRIMARY KEY, 
    target_id   INT, 
    base_amount DECIMAL(10, 2) 
        NOT NULL DEFAULT 0, 
    commission  DECIMAL(10, 2) 
        NOT NULL DEFAULT 0, 
    FOREIGN KEY(target_id) 
        REFERENCES sales.targets(target_id), 
    FOREIGN KEY(staff_id) 
        REFERENCES sales.staffs(staff_id),
);
 
INSERT INTO 
    sales.commissions(staff_id, base_amount, target_id)
VALUES
    (1,100000,2),
    (2,120000,1),
    (3,80000,3),
    (4,900000,4),
    (5,950000,5);

جدول sales.commissions مشخصات کارمندان فروش را ذخیره می‌کند، یعنی base_amount، target_id و commission. این جدول از طریق ستون target_id به جدول sales.targets متصل می‌شود.

هدف ما محاسبه کمیسیون‌های تمام کارکنان فروش بر اساس اهداف فروش آن‌ها است.

الف) مثال دستور UPDATE INNER JOIN در SQL Server

کد زیر از دستور UPDATE INNER JOIN برای محاسبه کمیسیون فروش برای تمام کارکنان فروش استفاده می‌کند:

UPDATE
    sales.commissions
SET
    sales.commissions.commission = 
        c.base_amount * t.percentage
FROM 
    sales.commissions c
    INNER JOIN sales.targets t
        ON c.target_id = t.target_id;

خروجی به این شکل است:

SELECT 
    *
FROM 
    sales.commissions;

 اگر دوباره برای مشاهده داده‌های جدول sales.commissions کد بنویسید، مشاهده خواهید کرد که مقادیر ستون commision به روز رسانی شده‌اند:

(5 rows affected)

نتایج

ب) مثال دستور UPDATE LEFT JOIN در SQL Server

فرض کنید دو کارمند بخش فروش دیگر داریم که به تازگی به گروه پیوستند و هنوز هیچ هدفی ندارند:

INSERT INTO 
    sales.commissions(staff_id, base_amount, target_id)
VALUES
    (6,100000,NULL),
    (7,120000,NULL);

فرض می‌کنیم که کمیسیون برای کارمندان فروش جدید 0.1 یا 10% باشد، می‌توانید کمیسیون تمام کارمندان فروش را با استفاده از دستور UPDATE LEFT JOIN به شکل زیر به روز رسانی کنیم:

UPDATE 
    sales.commissions
SET  
    sales.commissions.commission = 
        c.base_amount  * COALESCE(t.percentage,0.1)
FROM  
    sales.commissions c
    LEFT JOIN sales.targets t 
        ON c.target_id = t.target_id;

در این مثال، اگر درصد برابر با NULL باشد، از تابع ()COALESCE برای بازگرداندن 0.1 استفاده می‌کنیم:

توجه کنید که اگر از دستور UPDATE INNER JOIN استفاده کنید، فقط ۵ ردیف جدولی که اهداف آن NULL نیستند به روز رسانی خواهند شد.

اجازه دهید داده‌های موجود در جدول sales.commissions را امتحان کنیم:

2
3
4
SELECT 
  * 
FROM 
    sales.commissions;

مجموعه نتایج به شکل زیر است:

نتایج

در این بخش، چگونگی استفاده از دستور UPDATE JOIN در SQL Server برای اجرای به روز رسانی بین جدولی آشنا شدید.

دستور DELETE در SQL Server

برای حذف کامل یک یا تعداد بیشتری از ردیف‌های یک جدول، می‌توانید از دستور DELETE استفاده کنید. کد زیر، syntax این دستور را نشان می‌دهد:

DELETE [ TOP ( expression ) [ PERCENT ] ]  
FROM table_name
[WHERE search_condition];

اول، نام جدولی که ردیف‌های آن باید حذف شوند، در دستور FROM مشخص می‌شود.

برای مثال، کد زیر همه ردیف‌های جدول Target_table را حذف می‌کند:

DELETE FROM target_table;

دوم، برای مشخص کردن رقم درصد ردیف‌های تصادفی که حذف خواهند شد، می‌توانید از دستور TOP استفاده کنید. برای مثال، کد DELETE زیر ۱۰ ردیف تصادفی را از target_table حذف می‌کند:

DELETE TOP 10 FROM target_table;  

چون این جدول ردیف‌هایش را به صورت نامنظم ذخیره می‌کند، پس نمی‌دانیم که کدام ردیف‌ها حذف خواهند شد، اما مسلماً می‌دانیم که تعداد ردیف‌هایی که حذف خواهند شد، ۱۰ عدد است.

به طور مشابه، می‌توانید ۱۰ درصد از ردیف‌های تصادفی را با استفاده از کد DELETE زیر حذف کند:

DELETE TOP 10 PERCENT FROM target_table;

سوم، حقیقت این است که به ندرت پیش می‌آید که لازم شود تمام ردیف‌های یک جدول، اما فقط یک یا چند ردیف از آن حذف شوند. در این مورد، باید search_condition را در دستور WHERE مشخص کنید تا تعداد ردیف‌هایی که قرار است حذف شوند را محدود کنید.

ردیف‌هایی که باعث می‌شوند نتیجه search_condition برابر با TRUE شود، حذف خواهند شد.

استفاده از دستور WHERE اجباری نیست. اگر از آن استفاده نکنید، دستور DELETE تمام ردیف‌های جدول را حذف می‌کند.

مثال‌های دستور DELETE در SQL Server

اجازه دهید یک جدول جدید برای مثال‌ها ایجاد کنیم.

کد زیر یک جدول به نام production.product_history ایجاد می‌کند که داده‌های آن از جدول production.products کپی شده‌اند.

SELECT
    * 
INTO production.product_history
FROM
    production.products;

Query زیر تمام ردیف‌ها را از جدول product history بازمی‌گرداند:

SELECT
    *
FROM
    production.product_history;

همان‌طور که انتظار می‌رود، خروجی کد بالا باید در کل شامل ۳۲۱ ردیف باشد.

۱)مثال حذف تعداد تصادفی ردیف‌ها

کد DELETE زیر ۲۱ ردیف را به صورت تصادفی از جدول product history حذف می‌کند:

DELETE TOP (21)
FROM
    production.product_history;

پیغامی که SQl Server نشان می‌دهد این است:

(21 rows affected)

بدان معنا که ۲۱ ردیف حذف شده‌اند.

۲)مثال حذف درصدی از ردیف‌ها به صورت تصادفی

کد DELETE زیر ۵ درصد از ردیف‌ها را به صورت تصادفی از جدول product_history حذف می‌کند:

DELETE TOP (5) PERCENT
FROM 
    production.product_history;

SQL Server پیغام زیر را نمایش می‌دهد که بیان می‌کند ۱۵ ردیف (300*5% = 15) حذف شده‌اند.

(15 rows affected)
۳)مثال حذف چند ردیف با استفاده از یک شرط

کد DELETE زیر تمام محصولاتی که دارای سال مدل ۲۰۱۷ هستند را حذف می‌کند:

DELETE
FROM
    production.product_history
WHERE
    model_year = 2017;

خروجی به این شکل است:

(75 rows affected)
۴)مثال حذف تمام ردیف‌ها از یک جدول

کد DELETE زیر تمام ردیف‌های جدول product_history را حذف می‌کند:

DELETE
FROM
    production.product_history;

توجه کنید که اگر می‌خواهید تمام ردیف‌های یک جدول بزرگ را حذف کنید، باید از دستور TRUNCATE TABLE استفاده کنید که هم خیلی سریع‌تر و هم مؤثرتر است.

به این صورت، در این بخش به طور کامل چگونگی استفاده از دستور DELETE برای حذف یک یا تعداد بیشتری از ردیف‌های یک جدول را آموختید.

دستور MERGE در SQL Server

فرض کنید، دو جدول به نام منبع و هدف دارید و باید جدول target را بر اساس مقادیر مشابه با جدول منبع به روز رسانی کنید. سه حالت وجود دارند:

  1. جدول منبع دارای چند ردیف است که در جدول target وجود ندارند. در این حالت، باید ردیف‌هایی که در جدول منبع قرار دارند را به جدول target وارد (INSERT) کنید.
  2. جدول Target دارای چند ردیف است که در جدول منبع وجود ندارند. در این حالت، باید ردیف‌های جدول Target را حذف (DELETE) کنید.
  3. جدول منبع دارای چند ردیف با کلید مشابه با ردیف‌های جدول هدف می‌باشد. به هر حال، این ردیف‌ها دارای مقادیر مختلفی در ستون‌های غیر کلید هستند. در این حالت، باید ردیف‌های موجود در جدول هدف را با مقادیری که از جدول منبع می‌آیند، به روز رسانی (UPDATE) کنید.

تصویر زیر جداول منبع و هدف را با اعمال مشابه نمایش می‌دهد: یعنی با اعمال update، insert و delete:

دیاگرام جداول هدف و منبع

اگر به صورت انفرادی و تکی از دستورهای UPDATE، INSERT و DELETE استفاده کنید، مجبور می‌شوید سه کد مجزا برای به روز رسانی داده‌ها در جدول هدف با ردیف‌های مشابه در جدول منبع بنویسید.

به هر حال، SQL Server دستور MERGE را ارائه داده است که به شما امکان می‌دهد سه عمل را به صورت هم‌زمان انجام دهید.

کد زیر syntax دستور MERGE را نمایش می‌دهد:

MERGE target_table USING source_table
ON merge_condition
WHEN MATCHED
    THEN update_statement
WHEN NOT MATCHED
    THEN insert_statement
WHEN NOT MATCHED BY SOURCE
    THEN DELETE;

ابتدا، جدول هدف و جدول منبع را در دستور MERGE مشخص می‌کنید.

سپس، merge_condition مشخص می‌کند که چگونه ردیف‌های جدول منبع با ردیف‌های جدول هدف تطابق می‌یابند. این دستور شبیه به شرط اتصال در دستور join است. معمولاً، از ستون‌های کلید (کلید اصلی یا کلید منحصر به فرد) برای تطابق استفاده می‌کنند.

در آخر، merge_condition می‌تواند سه حالت داشته باشد: NOT MATCHED، MATCHED و NOT MATCHED BY SOURCE.

  • حالت MATCHED: این‌ها ردیف‌هایی هستند که با شرط merge تطابق دارند. در دیاگرام، این ردیف‌ها با رنگ آبی نمایش داده شده‌اند. برای ردیف‌های مطابق، باید ستون‌های آن ردیف‌ها را در جدول هدف با مقادیری از جدول منبع به روز رسانی کنید.

  • حالت NOT MATCHED: این‌ها ردیف‌های جدول منبع هستند که هیچ ردیف مشابهی در جدول هدف ندارند. در دیاگرام، این ردیف‌ها با رنگ نارنجی مشخص شده‌اند. در این حالت، باید ردیف‌هایی از جدول منبع را به جدول هدف اضافه کنید. توجه کنید که NOT MATCHED همچنین با نام NOT MATCHED BY TARGET نیز شناخته می‌شوند.

  • حالت NOT MATCHED BY SOURCE: این‌ها ردیف‌هایی در جدول هدف هستند که با هیچ ردیفی در جدول منبع تطابق ندارند. در دیاگرام این ردیف‌ها با رنگ سبز نمایش داده شده‌اند. اگر می‌خواهید جدول هدف را با داده‌هایی از جدول منبع همگام‌سازی (synchronize) کنید، پس باید از این شرط تطابق برای حذف ردیف‌ها از جدول هدف استفاده کنید.

مثال دستور MERGE در SQL Server

فرض کنید دو جدول به نام‌های sales.category و sales.category_staging داریم که فروش‌ها را بر اساس دسته فروش ذخیره می‌کنند.

CREATE TABLE sales.category (
    category_id INT PRIMARY KEY,
    category_name VARCHAR(255) NOT NULL,
    amount DECIMAL(10 , 2 )
);
 
INSERT INTO sales.category(category_id, category_name, amount)
VALUES(1,'Children Bicycles',15000),
    (2,'Comfort Bicycles',25000),
    (3,'Cruisers Bicycles',13000),
    (4,'Cyclocross Bicycles',10000);
 
 
CREATE TABLE sales.category_staging (
    category_id INT PRIMARY KEY,
    category_name VARCHAR(255) NOT NULL,
    amount DECIMAL(10 , 2 )
);
 
 
INSERT INTO sales.category_staging(category_id, category_name, amount)
VALUES(1,'Children Bicycles',15000),
    (3,'Cruisers Bicycles',13000),
    (4,'Cyclocross Bicycles',20000),
    (5,'Electric Bikes',10000),
    (6,'Mountain Bikes',10000);

برای به روز رسانی داده‌ها در جدول Sales.category (جدول هدف) با مقادیر جدول sales.category_staging (جدول منبع)، می‌توانید از دستور MERGE زیر استفاده کنید:

MERGE sales.category t 
    USING sales.category_staging s
ON (s.category_id = t.category_id)
WHEN MATCHED
    THEN UPDATE SET 
        t.category_name = s.category_name,
        t.amount = s.amount
WHEN NOT MATCHED BY TARGET 
    THEN INSERT (category_id, category_name, amount)
         VALUES (s.category_id, s.category_name, s.amount)
WHEN NOT MATCHED BY SOURCE 
    THEN DELETE;

عملکرد merge

در این مثال، ما از مقادیر موجود در ستون‌های category_id در هر دو جداول به عنوان شرط merge استفاده کردیم.

  • ابتدا، ردیف‌های دارای id به شماره 1, 3, 4 از جدول category_staging با ردیف‌ها از جدول هدف تطابق می‌یابند، در نتیجه، دستور MERGE مقادیر موجود در ستون‌های name و amount دسته در جدول Sales.category را به روز رسانی می‌کند.

  • سپس، ردیف‌های دارای id به شما ۵ و ۶ از جدول category_staging در جدول Sales.category وجود ندارند، بنابراین کد MERGE این ردیف‌ها را به جدول هدف وارد می‌کند.

  • در آخر، ردیف دارای id به شما ۲ از جدول category در جدول sales.category_staging وجود ندارد، در نتیجه، دستور MERGE این ردیف را حذف می‌کند.

در نتیجه این کد merge کننده، داده‌های موجود در جدول Sales.category کاملاً با داده‌های موجود در جدول Sales.category_staging همگام‌سازی (synchronize) می‌شوند.

به این شکل، چگونگی استفاده از دستور MERGE در SQL Server برای ایجاد تغییر در یک جدول بر اساس مقادیر مطابق از یک جدول دیگر را آموختید.

از آخرین دوره های آموزشی و تخفیف ها مطلع شوید

با تکمیل فرم زیر ، از اخبار و اطلاعات به روز برنامه نویسی و تکنولوژی عقب نمانید

آخرین مطالب

آموزش جامع SQL Server (جلسه ۱۲)
آموزش جامع SQL Server (جلسه ۱۲)

دستور UPDATE در SQL Server برای تغییر داده‌های موجود در یک جدول، از دستور UPDATE به شکل زیر استفاده ...

آموزش جامع SQL Server (جلسه ۱۵)
آموزش جامع SQL Server (جلسه ۱۵)

دستور DROP TABLE در SQL Server گاهی، لازم است یک جدول که دیگر استفاده‌ای ندارد را حذف کنید. برای ...

آموزش جامع SQL Server (جلسه ۳۵: Window Functionها – بخش ۲)
آموزش جامع SQL Server (جلسه ۳۵: Window Functionها – بخش ۲)

بخش اول از آخرین مبحث دوره جامع آموزش SQL Server در جلسه قبلی بررسی شد. این مبحث که ...

آخرین دیدگاه ها

دیدگاه خود را درباره این پست بنویسید

فرم ارسال نظرات