Як використовувати оператор UPDATE у SQL: приклади для різних СКБД

У цій статті ми розглянемо оператор SQL UPDATE і покажемо, як його застосовувати. Також подивимося на деякі відмінності у синтаксисі цього оператора в різних версіях SQL різних систем керування реляційними базами даних (СКРБД).

Більше про роботу з SQL для аналітики даних можна дізнатися на курсі Junior Data Analyst від robot_dreams.

Що таке оператор SQL UPDATE

Оператор UPDATE  — один з основних операторів мови керування даними DMLData Manipulation Language є підмножиною SQL. разом із SELECT, DELETE та INSERT.

Призначення та сфери застосування

Оператор UPDATE використовується для зміни рядків у таблиці бази даних. За допомогою цього оператора можна змінювати дані в одному або кількох стовпцях і обирати для цього:

• один рядок;
• кілька рядків;
• чи цілу таблицю.

Оператор може бути корисним для таких бізнес-завдань, як-от оновлення статусу замовлення, змінення етапу доставки чи адреси клієнта, збільшення заробітної плати працівника тощо.

Синтаксис UPDATE

Базовий синтаксис оператора UPDATE з використанням оператора умови WHERE такий:

UPDATE таблиця
SET стовпець1 = значення1, стовпець2 = значення2, ... стовпецьN = значенняN
[WHERE умова];

Аргументи/параметри

Щоб використовувати оператор UPDATE, потрібно вказати для нього такі параметри:

• таблиця — ім’я таблиці, у якій потрібно змінити дані;
• стовпець1… стовпецьN — імена стовпців, у яких потрібно змінити значення;
• значення1… значенняN — значення, які слід вставити замість попередніх значень у вказані стовпці.

Також після WHERE можна записати умову для вибору рядків. Якщо WHERE не використовувати, буде оновлено усі рядки таблиці.

Сумісність

Розробники СКБД створюють власні діалекти SQL, які розширюють стандартний SQL і дещо відрізняються як від нього, так й один від одного.

Наприклад, щоб обмежити кількість рядків, які потрібно змінити, певним числом або відсотком, в T-SQL у операторі UPDATE використовується оператор TOP, в MySQL — LIMIT, в Oracle та PostgreSQL — FETCH (див. Приклад 4 у кінці статті).

Відмінностей в діалектах SQL насправді набагато більше. Щоб вивчити їх досконало, приходьте на курс Junior Data Analyst від robot_dreams. Новий набір стартує вже 27 березня.

Розглянемо синтаксис UPDATE детальніше в таких діалектах SQL:

• Transact-SQL (T-SQL);
• MySQL;
• PostgreSQL;
• Oracle Database.

Transact-SQL (T-SQL)

Transact-SQL (T-SQL) є пропрієтарнимТобто має власника, на відміну від open-source рішень. розширенням мови SQL, створеним компанією Microsoft для використання в Microsoft SQL Server. Мова T-SQL підтримується в Microsoft Azure SQL Database і сумісна з СКБД Sybase.

Нижче наведено синтаксис UPDATE, який використовується в T-SQL:

-- Syntax for SQL Server and Azure SQL Database  

[ WITH <common_table_expression> [...n] ]  
UPDATE   
    [ TOP ( expression ) [ PERCENT ] ]   
    { { table_alias | <object> | rowset_function_limited   
         [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]  
      }  
      | @table_variable      
    }  
    SET  
        { column_name = { expression | DEFAULT | NULL }  
          | { udt_column_name.{ { property_name = expression  
                                | field_name = expression }  
                                | method_name ( argument [ ,...n ] )  
                              }  
          }  
          | column_name { .WRITE ( expression , @Offset , @Length ) }  
          | @variable = expression  
          | @variable = column = expression  
          | column_name { += | -= | *= | /= | %= | &= | ^= | |= } expression  
          | @variable { += | -= | *= | /= | %= | &= | ^= | |= } expression  
          | @variable = column { += | -= | *= | /= | %= | &= | ^= | |= } expression  
        } [ ,...n ]   
  
    [ <OUTPUT Clause> ]  
    [ FROM{ <table_source> } [ ,...n ] ]   
    [ WHERE { <search_condition>   
            | { [ CURRENT OF   
                  { { [ GLOBAL ] cursor_name }   
                      | cursor_variable_name   
                  }   
                ]  
              }  
            }   
    ]   
    [ OPTION ( <query_hint> [ ,...n ] ) ]  
[ ; ]  
  
<object> ::=  
{   
    [ server_name . database_name . schema_name .   
    | database_name .[ schema_name ] .   
    | schema_name .  
    ]  
    table_or_view_name}

MySQL

MySQL є вільною СКРБД з відкритим вихідним кодом, яку створила компанія MySQL AB. Нижче наведено синтаксис UPDATE, який використовується в MySQL. Синтаксис для однієї таблиці:

 UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

value:
    {expr | DEFAULT}

assignment:
    col_name = value

assignment_list:
    assignment [, assignment]

Синтаксис для кількох таблиць:

UPDATE [LOW_PRIORITY] [IGNORE] table_references
        SET assignment_list
        [WHERE where_condition]

PostgreSQL

PostgreSQL є об’єктно-реляційною СКБД, яка підтримує і реляційні (SQL), і нереляційні запити (JSON). Цю вільну СКБД створено на основі СКБД Postgres, яку розроблено в університеті Каліфорнії в Берклі. Нижче наведено синтаксис UPDATE, який використовується в PostgreSQL:

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    SET { column_name = { expression | DEFAULT } |
          (column_name [, ...]) = [ROW] ({expression | DEFAULT} [, ...]) |
          ( column_name [, ...] ) = ( sub-SELECT )
        } [, ...]
    [ FROM from_item [, ...] ]
    [ WHERE condition | WHERE CURRENT OF cursor_name]
    [RETURNING* | output_expression [ [ AS ] output_name ] [, ...] ]

Oracle Database

Oracle Database — це пропрієтарна комплексна хмарна об’єктно-реляційна СКБД. Її створила Oracle Corporation. Нижче наведено синтаксис UPDATE (схему того, як працюють запити), який використовується в Oracle Database:

Приклади роботи з оператором SQL UPDATE

Візьмемо за приклад таблицю з рейтингом СКБД з оцінки PYPLІндекс популярності мов програмування на запити в Google. за лютий 2023 р. Назвемо її db_ranking:

Rank	RDBMS	DBShare	Trend
1	Oracle	26.79	-3.7
2	MySQL	19.24	2.7
3	SQL Server	12.21	-1.3
4	PostgreSQL	6.48	1.6
5	Microsoft Access	6.42	-1.7

Для роботи з прикладами можна використовувати ресурс SQL Fiddle. На ньому представлено всі СКРБД, які зазначено вище. Обрати потрібну СКРБД можна в випадному списку праворуч від логотипу SQL Fiddle.

Для створення таблиці та наповнення її вихідними значеннями використовуємо такий код:

CREATE TABLE db_ranking (
  Rank int,
  RDBMS varchar(255),
  DBShare float,
  Trend float
 );

INSERT INTO db_ranking
 VALUES (1, 'Oracle', 26.79, -3.7);
INSERT INTO db_ranking
 VALUES (2, 'MySQL', 19.24, 2.7);
INSERT INTO db_ranking
 VALUES (3, 'SQL Server', 12.21, -1.3);
INSERT INTO db_ranking
 VALUES (4, 'PostgreSQL', 6.48, 1.6);
INSERT INTO db_ranking
 VALUES (5, 'Microsoft Access', 6.42, -1.7);

Введіть цей запит у лівій панелі (Schema Panel) і натисніть кнопку Build Schema.

Приклад 1. Змінимо частку Oracle на 30,49% (таке значення для цієї СКБД було у 2022 році)

У правій панелі введіть наступний код для зміни запису та його подальшого перегляду:

UPDATE db_ranking
SET DBShare = 30.49
WHERE RDBMS = 'Oracle';

SELECT *
FROM db_ranking
WHERE RDBMS = 'Oracle';

Результат виконання запиту:

Rank	RDBMS	DBShare	Trend
1	Oracle	30.49	-3.7

Приклад 2. Обнулимо негативні значення Trend для рядків з DBShare>10

Це завдання можна виконати в два способи — (1) з використанням оператора AND або (2) з використанням оператора OR — залежно від того, як ми розуміємо умови і який результат хочемо отримати.

Щоб використовувати оператор AND для цієї задачі, спочатку ми обнулимо значення Trend для всіх рядків таблиці, де це значення менше за нуль, а значення DBShare більше за 10. Потім переглянемо всі рядки таблиці.

Зверніть увагу: навіть якщо ви змінюєте запит у правій панелі SQL Fiddle, запит на створення таблиці буде виконано знову, тобто, дії буде виконано над вихідною таблицею.

UPDATE db_ranking
SET DBShare = 30.49
WHERE RDBMS = 'Oracle';

SELECT *
FROM db_ranking
WHERE RDBMS = 'Oracle';

Результат:

Rank	RDBMS	DBShare	Trend
1	Oracle	26.79	0
2	MySQL	19.24	2.7
3	SQL Server	12.21	0
4	PostgreSQL	6.48	1.6
5	Microsoft Access	6.42	-1.7

Якщо ми хочемо використовувати замість AND оператор OR, нам потрібно відібрати для зміни ті рядки вихідної таблиці, в яких Trend менше за нуль, або DBShare більше за 10. Запит матиме такий вигляд:

UPDATE db_ranking
SET Trend = 0
WHERE Trend < 0 AND DBShare > 10;

SELECT *
FROM db_ranking;

Результат:

Rank	RDBMS	DBShare	Trend
1	Oracle	26.79	0
2	MySQL	19.24	0
3	SQL Server	12.21	0
4	PostgreSQL	6.48	1.6
5	Microsoft Access	6.42	0

Дізнайтеся, який оператор SQL підходить краще за все для виконання вашого таску — приходьте на курс з аналітики даних від robot_dreams.

Приклад 3. Обнулимо значення DBShare і Trend для всіх рядків у таблиці

Пишемо простий запит на змінення даних, але, щоб його було застосовано до всіх рядків таблиці, не використовуємо оператор WHERE:

UPDATE db_ranking
SET DBShare = 0; Trend = 0;

SELECT *
FROM db_ranking;

Результат:

Rank	RDBMS	DBShare	Trend
1	Oracle	0	0
2	MySQL	0	0
3	SQL Server	0	0
4	PostgreSQL	0	0
5	Microsoft Access	0	0

Приклад 4. Змінимо N рядків у довільному порядку

Для роботи з T-SQL виберіть MS SQL Server у списку праворуч від логотипу.

Запит TOP(n) оператора UPDATE дозволяє вибрати з таблиці n рядків у довільному порядку. Приклад:

UPDATE TOP(10) employees
SET vacation_hours = vac
ation_hours * 1.25;

Також можна відібрати певний відсоток рядків. Це робиться за допомогою аргументу PERCENT:

UPDATE TOP(10) PERCENT employees
SET vacation_hours = vacation_hours * 1.25;

До нашої таблиці можна надіслати, наприклад, такий запит на обнулення рейтингу у трьох рядках у довільному порядку:

UPDATE TOP(3) db_ranking
SET Rank = 0;

Ось що можемо отримати в результаті запиту:

У MySQL для обмеження кількості рядків використовується оператор LIMIT. Код для MySQL матиме такий вигляд (у SQL Fiddle його необхідно ввести в лівій панелі після операторів INSERT):

UPDATE db_ranking
SET Rank = 0LIMIT 3;

У PostgreSQL і Oracle Database використовується оператор FETCH із запитом SELECT:

UPDATE db_ranking
SET Rank = 0
WHERE RDBMS IN (SELECT RDBMS
    FROM db_ranking
    FETCH FIRST 3 ROWS ONLY);

Висновок

Ми розглянули базові приклади застосування оператора SQL UPDATE.

Оператор UPDATE дозволяє змінювати значення в комірках таблиці з зазначенням стовпців та рядків, у які буде внесено зміни. Базовий синтаксис цього оператора в різних діалектах SQL загалом схожий, але є й відмінності, характерні для окремих СКРБД.

Детальну інформацію про особливості використання оператора UPDATE у розширеннях SQL, які згадано в цій статті, ви можете знайти в посібниках із Transact-SQL, MySQL, PostgreSQL та Oracle Database.

Наостанок пропонуємо вам закріпити знання та переглянути відео про використання оператора UPDATE:

А якщо хочете вивчити SQL глибше і під наглядом досвідчених менторів, завітайте до robot_dreams. На їхньому курсі ви зможете опанувати професію Junior Data Analyst за 16 тижнів.