Статистическое выражение нельзя использовать в списке set инструкции update

Всем привет! Сегодня в материале я расскажу о том, как происходит обновление данных в Microsoft SQL Server с использованием языка T-SQL, будет рассмотрена инструкция UPDATE. Иными словами, Вы узнаете, как изменить уже существующие данные в таблицах на SQL Server.

Ранее я уже рассказывал о том, как создаются таблицы, как они изменяются, как происходит добавление данных в эти таблицы, поэтому пришло время рассказать о том, как происходит изменение (обновление) существующих данных.

Заметка!

Упомянутые выше материалы:

• Создание таблиц в Microsoft SQL Server (CREATE TABLE);
• Изменение таблиц в Microsoft SQL Server (ALTER TABLE);
• Добавление данных в таблицы (INSERT INTO).

Заметка! Для комплексного изучения языка T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, в которых используется последовательная методика обучения и рассматриваются все конструкции языка SQL и T-SQL.

Изменить данные в таблицах Microsoft SQL Server можно с помощью:

• Инструкции UPDATE;
• Конструктора SQL Server Management Studio (SSMS).

Однако в реальности обновление данных происходит только с использование инструкции UPDATE, так как использовать конструктор SSMS для каких-то массовых изменений невозможно, даже процесс обновления одного значения крайне неудобен. Поэтому я не использую конструктор, мне кажется, его мало кто использует.

Поэтому сегодня мы подробно поговорим об инструкции UPDATE.

Описание инструкции UPDATE в T-SQL

UPDATE – инструкция SQL, с помощью которой происходит изменение существующих данных в таблицах.

Важные моменты:

• Если инструкция UPDATE, т.е. обновление строк, нарушает какое-нибудь ограничение или правило, или новое значение имеет несовместимый тип данных (хотя бы для одной строки), то возникнет ошибка и все изменения отменяются, никакие строки не обновляются;
• По умолчанию инструкция UPDATE получает монопольную блокировку на целевую таблицу, которую она изменяет, это означает, что пока одна инструкция UPDATE выполняется, т.е. изменяет данные в таблице, другие инструкции не могут изменять данные в этой таблице;
• Чтобы использовать инструкцию UPDATE, нужны соответствующие разрешения на изменение данных, а также на чтение данных, если инструкция содержит условие WHERE;
• Если Вам нужно узнать количество строк, которые Вы обновили инструкцией UPDATE, например, для возврата в клиентское приложение или для любых других целей, то для этого Вы можете использовать функцию @@ROWCOUNT.

Упрощенный синтаксис UPDATE

Синтаксис UPDATE достаточно большой, и начинающим понять его сложно, поэтому, для того чтобы было проще понять логику формирования инструкции UPDATE, я приведу упрощенный синтаксис.

UPDATE Целевая таблица SET Имя столбца = Значение

FROM Таблица источник

WHERE Условие

Где

• UPDATE – инструкция обновления;
• Целевая таблица – таблица, данные в которой необходимо изменить;
• SET – команда, которая задает список обновляемых столбцов. Каждый следующий столбец указывается через запятую;
• Имя столбца – столбец, в котором расположены данные, которые необходимо изменить;
• Значение – новое значение, на которое необходимо изменить значение столбца. Можно указывать как конкретное значение, так и расчётное выражение, функцию или подзапрос. Также можно указать ключевое слово DEFAULT, что будет означать, что столбцу необходимо присвоить значение по умолчанию;
• FROM – секция, которая указывает таблицу, из которой необходимо взять новое значение столбца. Секция может содержать объединение JOIN;
• Таблица источник – таблица, в которой расположено новое значение столбца;
• WHERE – условие отбора строк, подлежащих обновлению.

Примеры использования инструкции UPDATE

Сейчас давайте рассмотрим несколько примеров SQL инструкций, которые будут обновлять данные в таблице инструкцией UPDATE.

Исходные данные для примеров

Для начала давайте определимся с исходными данными, чтобы Вы понимали, какие именно данные у нас есть, и что мы будем обновлять.

Также сразу скажу, что в качестве SQL сервера у меня выступает версия Microsoft SQL Server 2017 Express.

Следующая инструкция создает таблицы, которые мы будет использовать в примерах, и добавляет в них данные.

   
   --Создание таблицы Goods
   CREATE TABLE Goods (
        ProductId       INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_ProductId PRIMARY KEY,
        Category        INT NOT NULL,
        ProductName     VARCHAR(100) NOT NULL,
        ProductDescription VARCHAR(300) NULL,
        Price           MONEY NULL,
   );
   GO

   --Создание таблицы Categories
   CREATE TABLE Categories (
        CategoryId   INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_CategoryId PRIMARY KEY,
        CategoryName VARCHAR(100) NOT NULL
   );

   --Добавление строк в таблицу Categories
   INSERT INTO Categories(CategoryName)
        VALUES ('Комплектующие ПК'),
                   ('Мобильные устройства');
   GO

   --Добавление строк в таблицу Goods
   INSERT INTO Goods(Category, ProductName, ProductDescription, Price)
        VALUES (1, 'Системный блок', 'Товар 1', 300),
                   (1, 'Монитор', 'Товар 2', 200),
                   (2, 'Смартфон', 'Товар 3', 100);
   GO

   --Выборка данных
   SELECT * FROM Goods;
   SELECT * FROM Categories;

Пример обновления одного столбца всех строк таблицы

В этом примере мы обновим значения одного столбца, при этом никаких условий мы делать не будем, т.е. обновим все строки в таблице.

Для наглядности и удобства отслеживания внесенных изменений я буду во всех примерах перед и после UPDATE посылать простой запрос SELECT, чтобы видеть, какие данные были и какие стали.

   
   --Выборка данных
   SELECT * FROM Goods;

   --Обновление
   UPDATE Goods SET ProductDescription = 'Товар';

   --Выборка данных
   SELECT * FROM Goods;

Как видите, в итоге получился очень простой запрос на обновление, мы обновили значения в столбце ProductDescription у всех строк на «Товар».

Пример обновления двух столбцов и только некоторых строк таблицы

Теперь давайте обновим два столбца, и конкретизируем строки для обновления, т.е. мы обновим не все строки в таблице, как в предыдущем примере, а только те, которые подходят под указанное нами условие (для примера Category с идентификатором 1).

   
   --Выборка данных
   SELECT * FROM Goods;

   --Обновление
   UPDATE Goods SET ProductDescription = 'Товар NEW',
                                 Price = 400
   WHERE Category = 1;

   --Выборка данных
   SELECT * FROM Goods;

В этом случае изменились значения столбцов ProductDescription и Price в строках, в которых Category = 1.

Пример использования выражений в инструкции UPDATE

Как я уже отмечал, в качестве нового значения может выступать не только какое-то конкретное значение, но и целое выражение, в котором могут использоваться как другие столбцы таблицы, так и столбец, который в данный момент обновляется.

В следующем примере в столбец ProductDescription мы добавим дополнительный текст (просто цифру 3), а значение Price мы увеличим в полтора раза. Все это мы сделаем для строки с Category = 2.

   
   --Выборка данных
   SELECT * FROM Goods;

   --Обновление
   UPDATE Goods SET ProductDescription = ProductDescription + ' 3',
                                 Price = Price * 1.5
   WHERE Category = 2;

   --Выборка данных
   SELECT * FROM Goods;

Пример обновления данных таблицы на основе данных другой таблицы

Достаточно часто требуется обновить данные одной таблицы на основе данных другой, например, просто скопировать данные. Это можно сделать за счет объединения нужных таблиц в инструкции UPDATE. При этом существует несколько способов объединения, я покажу два.

Для примера здесь мы скопируем название категорий из таблицы Categories, и вставим их в столбец ProductDescription таблицы Goods, объединять будем по идентификатору категории.

   
   --Выборка данных
   SELECT * FROM Goods;

   --Обновление
   --Способ 1
   UPDATE G SET ProductDescription = C.CategoryName
   FROM Goods G
   INNER JOIN Categories C ON G.Category = C.CategoryId;        

   --Способ 2 (эквивалент)
   UPDATE Goods SET ProductDescription = C.CategoryName
   FROM Categories C
   WHERE Goods.Category = C.CategoryId; 

   --Выборка данных
   SELECT * FROM Goods;

Пример обновления данных с использованием подзапроса

В этом примере я покажу, как можно использовать подзапрос в инструкции UPDATE. Для примера мы подсчитаем количество товаров в каждой категории и присвоим полученное значение столбцу ProductDescription.

Для того чтобы узнать количество товаров, мы будем использовать встроенную функцию COUNT, а для преобразования числа в строку — функцию CAST. Фильтровать строки в подзапросе мы будем по идентификатору категории, значение для сравнения будем получать из основного запроса.

   
   --Выборка данных
   SELECT * FROM Goods;

  --Обновление
  UPDATE Goods SET ProductDescription = 'Всего товаров: ' + (SELECT CAST(COUNT(*) AS VARCHAR(10))
                                                        FROM Goods G 
                                                        WHERE G.Category = Goods.Category);

   --Выборка данных
   SELECT * FROM Goods;

Как видите, все отработало так, как мы задумали.

Видео-инструкция по обновлению данных в Microsoft SQL Server

У меня на этом все, надеюсь, материал был Вам полезен, пока!