Рубріки: Теория

Как использовать оператор UPDATE в SQL: примеры для разных СУБД

Андрій Денисенко

10.03.2023 13:14

Содержание

Что такое оператор SQL UPDATE
Назначение и сферы применения
Синтаксис UPDATE
Аргументы/параметры
Совместимость
Примеры работы с оператором SQL UPDATE
Заключение

В этой статье мы рассмотрим оператор 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) — это проприетарноечастное расширение языка SQL, созданное компанией Microsoft для использования в Microsoft SQL Server. T-SQL поддерживается в Microsoft Azure SQL Database и совместим с СУБД Sybase.

Ниже приведен синтаксис UPDATE, используемый в T-SQL:

-- Syntax for SQL Server and Azure SQL Database  

[ WITH  [...n] ]  
UPDATE   
    [ TOP ( expression ) [ PERCENT ] ]   
    { { table_alias | <object width="300" height="150"> | 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 { += | -= | *= | /= | %= | &amp;= | ^= | |= } expression  
          | @variable { += | -= | *= | /= | %= | &amp;= | ^= | |= } expression  
          | @variable = column { += | -= | *= | /= | %= | &amp;= | ^= | |= } 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:

Схема оператора UPDATE из документации Oracle

Примеры работы с оператором 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.

Интерфейс 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 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	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 OR 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 = vacation_hours * 1.25;

Также можно отобрать и определенный процент строк. Это делается с помощью аргумента PERCENT:

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

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

UPDATE TOP(3) db_ranking
SET Rank = 0;

Вот что можем получить в результате исполнения запроса:

T-SQL: TOP

В MySQL для ограничения количества строк используется оператор LIMIT. Код для MySQL будет выглядеть так (в SQL Fiddle его необходимо ввести в левой панели после операторов INSERT):

UPDATE db_ranking
SET Rank = 0
LIMIT 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 недель.