Обновление sql из одной таблицы в другую на основе совпадения идентификатора

Зачем использовать ORDER с пунктом Update?

Вы можете использовать значение ORDER BY во время обновления. Предложения ORDER BY, безусловно, имеет смысл во время SELECT. Но, зачем нам нужен ORDER BY во время обновления.

Скажем, у вас есть уникальный идентификатор и поле идентификатор сотрудника таблицы.

Когда вы выполните следующую команду, чтобы увеличить идентификатор сотрудника на 100, вы можете получить сообщение об ошибке дубликатом.

mysql> UPDATE contractor set id=id+100;
ERROR 1062 (23000): Duplicate entry '200' for key 'PRIMARY'

Это происходит потому, что, когда он пытается обновить значение идентификатора от 100 до 200, то уже есть существующая запись с идентификатором, как 200. Поле ID также имеет уникальный UNIQUE, в данном случае это является PRIMARY ключом. Таким образом, мы получаем вышеуказанную ошибку.

Для этого мы должны выполнить следующую команду с ORDER BY с идентификатором Desc.

mysql> UPDATE worker SET id=id+100 order by id desc;
Query OK, 6 rows affected (0.01 sec)
Rows matched: 7  Changed: 7  Warnings: 0

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

Руководство по MySQL
544 страницы · 2016 · 4.78 MB · русский
by Тахагхогхи С. & Вильямс Хью Е.

MySQL по максимуму
866 страниц · 2018 · 102.51 MB · русский
by Бэрон Шварц & Петр Зайцев & Вадим Ткаченко

на главную сниппетов

2013-2021 «OIS» aka Coder

SQL SELECT TOP

Инструкция SELECT TOP используется для указания количества возвращаемых записей.

Инструкция SELECT TOP полезно для больших таблиц с тысячами записей. Возврат большого количества записей может повлиять на производительность.

Примечание: Не все базы данных поддерживают SELECT TOP.
MySQL поддерживает предложение LIMIT для выбора ограниченного числа записей, в то время как Oracle использует ROWNUM.

Синтаксис SQL Server / MS Access:

SELECT TOP number|percent column_name(s)
FROM table_nameWHERE condition;

Синтаксис MySQL:

SELECT column_name(s)
FROM table_nameWHERE condition
LIMIT number;

Синтаксис Oracle:

SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;

Случай объединения или IF с Update

Вы также можете использовать условные обновления MySQL с помощью условных команд, как CASE, IF и т.д. Это полезно для упрощения обновления.

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

Например, скажем, у нас есть следующие три оператора обновления, которые обновляют поле заработной платы на основе значения из поля отдела.

UPDATE worker SET salary = salary+1000 WHERE dept = 'Sales';
UPDATE worker SET salary = salary+500 WHERE dept = 'IT        ';
UPDATE worker SET salary = salary+800 WHERE dept = 'Marketing';

Вы можете объединить все перечисленные выше заявления трех UPDATE в одном заявлении UPDATE используя условие CASE, как показано ниже.

UPDATE worker SET salary = 
  CASE dept
   WHEN 'Sales' THEN salary+1000
   WHEN 'IT        ' THEN salary+500
   WHEN 'Marketing' THEN salary+500
   ELSE salary 
  END;

Так же, как CASE, вы можете также использовать IF условие для обновления значения столбца соответственно.

SQL References

SQL Keywords
ADD
ADD CONSTRAINT
ALTER
ALTER COLUMN
ALTER TABLE
ALL
AND
ANY
AS
ASC
BACKUP DATABASE
BETWEEN
CASE
CHECK
COLUMN
CONSTRAINT
CREATE
CREATE DATABASE
CREATE INDEX
CREATE OR REPLACE VIEW
CREATE TABLE
CREATE PROCEDURE
CREATE UNIQUE INDEX
CREATE VIEW
DATABASE
DEFAULT
DELETE
DESC
DISTINCT
DROP
DROP COLUMN
DROP CONSTRAINT
DROP DATABASE
DROP DEFAULT
DROP INDEX
DROP TABLE
DROP VIEW
EXEC
EXISTS
FOREIGN KEY
FROM
FULL OUTER JOIN
GROUP BY
HAVING
IN
INDEX
INNER JOIN
INSERT INTO
INSERT INTO SELECT
IS NULL
IS NOT NULL
JOIN
LEFT JOIN
LIKE
LIMIT
NOT
NOT NULL
OR
ORDER BY
OUTER JOIN
PRIMARY KEY
PROCEDURE
RIGHT JOIN
ROWNUM
SELECT
SELECT DISTINCT
SELECT INTO
SELECT TOP
SET
TABLE
TOP
TRUNCATE TABLE
UNION
UNION ALL
UNIQUE
UPDATE
VALUES
VIEW
WHERE

MySQL Functions
String Functions:
ASCII
CHAR_LENGTH
CHARACTER_LENGTH
CONCAT
CONCAT_WS
FIELD
FIND_IN_SET
FORMAT
INSERT
INSTR
LCASE
LEFT
LENGTH
LOCATE
LOWER
LPAD
LTRIM
MID
POSITION
REPEAT
REPLACE
REVERSE
RIGHT
RPAD
RTRIM
SPACE
STRCMP
SUBSTR
SUBSTRING
SUBSTRING_INDEX
TRIM
UCASE
UPPER

Numeric Functions:
ABS
ACOS
ASIN
ATAN
ATAN2
AVG
CEIL
CEILING
COS
COT
COUNT
DEGREES
DIV
EXP
FLOOR
GREATEST
LEAST
LN
LOG
LOG10
LOG2
MAX
MIN
MOD
PI
POW
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SUM
TAN
TRUNCATE

Date Functions:
ADDDATE
ADDTIME
CURDATE
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURTIME
DATE
DATEDIFF
DATE_ADD
DATE_FORMAT
DATE_SUB
DAY
DAYNAME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
EXTRACT
FROM_DAYS
HOUR
LAST_DAY
LOCALTIME
LOCALTIMESTAMP
MAKEDATE
MAKETIME
MICROSECOND
MINUTE
MONTH
MONTHNAME
NOW
PERIOD_ADD
PERIOD_DIFF
QUARTER
SECOND
SEC_TO_TIME
STR_TO_DATE
SUBDATE
SUBTIME
SYSDATE
TIME
TIME_FORMAT
TIME_TO_SEC
TIMEDIFF
TIMESTAMP
TO_DAYS
WEEK
WEEKDAY
WEEKOFYEAR
YEAR
YEARWEEK

Advanced Functions:
BIN
BINARY
CASE
CAST
COALESCE
CONNECTION_ID
CONV
CONVERT
CURRENT_USER
DATABASE
IF
IFNULL
ISNULL
LAST_INSERT_ID
NULLIF
SESSION_USER
SYSTEM_USER
USER
VERSION

SQL Server Functions
String Functions:
ASCII
CHAR
CHARINDEX
CONCAT
Concat with +
CONCAT_WS
DATALENGTH
DIFFERENCE
FORMAT
LEFT
LEN
LOWER
LTRIM
NCHAR
PATINDEX
QUOTENAME
REPLACE
REPLICATE
REVERSE
RIGHT
RTRIM
SOUNDEX
SPACE
STR
STUFF
SUBSTRING
TRANSLATE
TRIM
UNICODE
UPPER

Numeric Functions:
ABS
ACOS
ASIN
ATAN
ATN2
AVG
CEILING
COUNT
COS
COT
DEGREES
EXP
FLOOR
LOG
LOG10
MAX
MIN
PI
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SQUARE
SUM
TAN

Date Functions:
CURRENT_TIMESTAMP
DATEADD
DATEDIFF
DATEFROMPARTS
DATENAME
DATEPART
DAY
GETDATE
GETUTCDATE
ISDATE
MONTH
SYSDATETIME
YEAR

Advanced Functions
CAST
COALESCE
CONVERT
CURRENT_USER
IIF
ISNULL
ISNUMERIC
NULLIF
SESSION_USER
SESSIONPROPERTY
SYSTEM_USER
USER_NAME

MS Access Functions
String Functions:
Asc
Chr
Concat with &
CurDir
Format
InStr
InstrRev
LCase
Left
Len
LTrim
Mid
Replace
Right
RTrim
Space
Split
Str
StrComp
StrConv
StrReverse
Trim
UCase

Numeric Functions:
Abs
Atn
Avg
Cos
Count
Exp
Fix
Format
Int
Max
Min
Randomize
Rnd
Round
Sgn
Sqr
Sum
Val

Date Functions:
Date
DateAdd
DateDiff
DatePart
DateSerial
DateValue
Day
Format
Hour
Minute
Month
MonthName
Now
Second
Time
TimeSerial
TimeValue
Weekday
WeekdayName
Year

Other Functions:
CurrentUser
Environ
IsDate
IsNull
IsNumeric

SQL Quick Ref

оконные функции

Начиная с MariaDB 10.2 / MySQL 8 добавлена поддержка оконных функций. С помощью row_number() можно для каждого пользователя сделать отдельную нумерацию сообщений в порядке убывания даты. После чего выбрать те записи, у которых № меньше или равен 3.

select post_id, user_id, date_added, post_text from (select posts.*,
row_number() over (partition by user_id order by date_added desc) ifrom posts) t where i <= 3;

Производительность — двойное сканирование таблицы: сначала для нумерации (нет возможности ограничиться нумерацией только нескольких строк из группы), потом отбросить не удовлетворяющие условию where i <= 3.

Для случайных сообщений пользователя достаточно заменить сортировку по убыванию даты order by date_added desc на случайную — order by rand().

Шаг 2: Выберите свой входной файл (XLSX) и проверьте данные

Если вы уже запускали этот мастер раньше, вы можете выбрать файлы из предыдущих сеансов.

Когда вы выберете файл, мы захватим первые 100 строк для просмотра ниже. Этот «Предел строк предварительного просмотра» определяет, сколько строк вы можете использовать для проверки ИМПОРТА по мере прохождения мастера. Вы можете увеличить его, но это потребует больше ресурсов, так что не сходите с ума.

Кроме того, есть ли в вашем файле Excel заголовки столбцов? Хотим ли мы рассматривать их как строку к таблице? Скорее всего, нет. Если вы снимите флажок «Заголовок», имена столбцов станут новой строкой в вашей таблице – и, вероятно, не будут вставлены.

Иногда ваш файл Excel имеет несколько заголовков, или вам может потребоваться импортировать только определенное подмножество электронной таблицы. Используйте опцию «Пропустить строки», чтобы получить правильные данные.

пользовательские переменные

Та же идея, что и в предыдущем варианте, только реализована с помощью пользовательских переменных (user variables). Актуально для версий, в которых нет оконных функций.

select post_id, user_id, date_added, post_text from(select posts.*, if(@gr=user_id, @i:=@i+1, @i:=1 + least(@gr:=user_id,)) xfrom posts, (select @i:=, @gr:=) t order by user_id, date_added desc) t1 where x <=3;

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

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

select post_id, user_id, date_added, post_text from(select t2.*, if(@gr=user_id, @i:=@i+1, @i:=1 + least(@gr:=user_id,)) xfrom (select posts.*, rand() q, @z:=1 from posts) t2, (select @i:=, @gr:=) t order by user_id, q) t1 where x <=3

Обратите внимание на добавление ещё одной переменной @z:=1, которая более нигде не применяется. С некоторых пор оптимизатор научился упрощать тривиальные с его точки зрения from-подзапросы, перенося условия из них во внешний запрос

Однако, если в подзапросе используются переменные, то пока оптимизатор материализует такие подзапросы.

В общем, пользовательские переменные — мощный инструмент написания и оптимизации запросов, но нужно быть очень внимательными при работе с ними, понимать на каком эффекте основан, используемый вами трюк, и проверять работоспособность в новых версиях. Подробнее см Оптимизация запросов MySQL с использованием пользовательских переменных

Множественное обновление таблиц (с помощью Left Join)

Аналогично предыдущему примеру внутреннего соединения, мы можем также использовать левое соединение.

Когда мы используем внутреннее соединение, мы не указываем ключевое слово “inner join”, так как это по умолчанию при объединении нескольких таблиц.

Однако при использовании соединения слева мы должны явно указать “left join”, как показано ниже.

mysql> UPDATE worker LEFT JOIN benefits on worker.dept = benefits.dept
    -> SET worker.salary = worker.salary+500 
    -> WHERE benefits.bonus is null;
Query OK, 2 rows affected (0.03 sec)
Rows matched: 2  Changed: 2  Warnings: 0

Вот выход после вышеуказанного обновления.

mysql> SELECT * FROM worker;
+-----+--------+------------+--------+
| id  | name   | dept       | salary |
+-----+--------+------------+--------+
| 100 | Andrey | Sales      |   7500 |
| 200 | Anton  | Sales      |   7500 |
| 300 | Maxim  | IT         |   7000 |
| 400 | Dimon  | IT         |   5500 |
| 500 | Anton  | Marketing  |   5800 |
| 501 | Anna   | Marketing  |   5800 |
+-----+--------+------------+--------+

Практическое упражнение №4:

На основании таблицы employees ниже, модифицируйте тип данных столбца employee_name в varchar2 (80).

Oracle PL/SQL

CREATE TABLE employees
( employee_number number(10) not null,
employee_name >varchar2(50) not null,
department_id number(10),
CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);

1
2
3
4
5
6

CREATETABLEemployees
(employee_numbernumber(10)notnull,

employee_name>varchar2(50)notnull,

department_idnumber(10),

CONSTRAINTemployees_pkPRIMARYKEY(employee_number)
);

Решение для практического упражнения №4:

Следующий оператор Oracle/PLSQL ALTER TABLE модифицирует тип данных для столбца employee_name в varchar2 (80):

Oracle PL/SQL

ALTER TABLE employees
MODIFY employee_name varchar2(80);

1 2	ALTERTABLEemployees MODIFYemployee_namevarchar2(80);

UPDATE несколько записей

Именно предложение WHERE определяет, сколько записей будет обновлено.

Следующая инструкция SQL обновит имя контакта до «Juan» для всех записей, где страна — «Mexico»:

Пример

UPDATE Customers
SET ContactName=’Juan’
WHERE Country=’Mexico’;

Выбор из таблицы «Customers» теперь будет выглядеть следующим образом:

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Alfred Schmidt	Obere Str. 57	Frankfurt	12209	Germany
2	Ana Trujillo Emparedados y helados	Juan	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Juan	Mataderos 2312	México D.F.	05023	Mexico
4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK
5	Berglunds snabbköp	Christina Berglund	Berguvsvägen 8	Luleå	S-958 22	Sweden

Практическое упражнение №7:

На основании таблицы departments ниже, переименовать столбец department_name в dept_name.

Oracle PL/SQL

CREATE TABLE departments
( department_id number(10) not null,
department_name varchar2(50) not null,
CONSTRAINT departments_pk PRIMARY KEY (department_id)
);

1
2
3
4
5

CREATETABLEdepartments
(department_idnumber(10)notnull,

department_namevarchar2(50)notnull,

CONSTRAINTdepartments_pkPRIMARYKEY(department_id)
);

Решение для практического упражнения №7:

Следующий оператор Oracle/PLSQL ALTER TABLE переименует столбец department_name в dept_name в таблице departments:

Oracle PL/SQL

ALTER TABLE departments
RENAME COLUMN department_name to dept_name;

1 2	ALTERTABLEdepartments RENAMECOLUMNdepartment_nametodept_name;

Узнайте как использовать оператор ALTER TABLE в распространенных БД:

ALTER TABLE SQL Server
ALTER TABLE MySQL
ALTER TABLE PostgreSQL
ALTER TABLE MariaDB
ALTER TABLE SQLite

Ограничение колличества записей при обновлении

Мы также можем использовать опцию LIMIT, чтобы ограничить количество записей, которые должны быть обновлены.

Даже если условие, где соответствует несколько записей, оператор обновления будет обновлять только 1-е X количество записей, указанных в значении LIMIT.

В следующем примере мы присваиваем зарплату всех записей до 6500, так как мы не имеем WHERE. Но, мы используем LIMIT 3. Это означает, что она будет обновлять зарплату только первым трем записям для условия согласования.

mysql> UPDATE worker SET salary=6500 LIMIT 3;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

Как видно из следующего выхода, только 1-е три записи обновляются предыдущей командой.

mysql> SELECT * FROM worker;
+-----+--------+-----------+--------+
| id  | name   | dept      | salary |
+-----+--------+-----------+--------+
| 100 | Andrey | Sales     |   6500 |
| 200 | Anton  | Sales     |   6500 |
| 300 | Maxim  | Sales     |   6500 |
| 400 | Dimon  | Marketing |   5000 |
| 500 | Anton  | Marketing |   5000 |
| 501 | Anna   | Marketing |   5000 |
+-----+--------+-----------+--------+

Одна вещь, чтобы иметь в виду, что LIMIT 3 на самом деле не означает, что обновление будет продолжать идти, пока он не обновит 3 записей. Вместо этого оператор обновления прекратит выполнение, как только он обработал первые 3 строки, которые соответствовали условию выполнения, независимо от того, были ли действительно обновлены эти записи или нет.

Вставка строк, некоторые из которых уже существуют в целевой таблице

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

Игнорирование INSERT IGNORE INTO

Например если мы вставляем строку с PK = 1, и при этом в таблице уже есть PK = 1 то MySQL выдаст ошибку:

/* ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' */

Выполнение запроса на этом прервется, однако нам в некоторых случаях хотелось бы просто вставить данные, игнорируя ошибки. В этом нам поможет INSERT IGNORE INTO:

INSERT IGNORE INTO table1 (a, b, c) VALUES 
    (1, 222, 333), 
    (2, 555, 666),
    (3, 555, 333);

Просто добавляем IGNORE в наш запрос и ошибки будут игнорироваться

Вставка с заменой существующих значений REPLACE INTO

REPLACE работает также INSERT, но если совпадают уникальные ключи, то старая строка (или строки!) удаляется до вставки новой.

В таком случае наш пример выглядит следующим образом:

REPLACE INTO table1 (a, b, c) VALUES 
    (1, 222, 333), 
    (2, 555, 666),
    (3, 555, 333);

Обновление некоторых полей, при существовании строк ON DUPLICATE KEY UPDATE

При совпадении ключей, мы можем также заменить некоторые или все поля в строке.

Наш запрос будет выглядеть так:

INSERT INTO table1 (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=VALUES(c);

В данном примере если у нас какой-то уникальный ключ совпадает, то мы не производим вставку, а обновляем существующую строку или строки путем присваивания столбцу «c» значения, которое у нас перечислено в VALUES.

Иными словами, если ключ совпадает, то мы просто обновим данные столбца «с» а остальные столбцы трогать не будем.

Иногда нам нужно при совпадении ключей обновить все значения. Этом можно сделать просто перечислив все столбцы:

INSERT INTO table1 (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE a=VALUES(a), b=VALUES(b), c=VALUES(c);

При обновлении столбцов мы также можем использовать разные выражения, например:

INSERT INTO table1 (a,b,c) VALUES (1,2,3),(4,5,6)
ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

Подробнее про ON DUPLICATE KEY UPDATE

MySQL error 1093

В MySQL нельзя изменять данные и одновременно делать выборку из той же таблицы в подзапросе. Запросы вида

DELETE FROM t WHERE col = (SELECT … FROM t …);UPDATE t … WHERE col = (SELECT … FROM t …);{INSERT|REPLACE} INTO t (SELECT … FROM t …);
ERROR 1093 (HY000): You can’t specify target table ‘t’ for update in FROM clause.

Есть два варианта решения проблемы:

1. Универсальный способ, рекомендуемый в документации, — использовать вложенный подзапрос.

DELETE FROM t WHERE col = (SELECT * FROM (SELECT … FROM t…) AS t1);UPDATE t … WHERE col = (SELECT * FROM (SELECT … FROM t…) AS t1);{INSERT|REPLACE} INTO t (SELECT * FROM (SELECT … FROM t…) AS t1);

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

2. Для запросов UPDATE и DELETE можно использовать многотабличную форму. Например, для UPDATE запрос выше примет вид:

UPDATE t, (SELECT … FROM t …) t1 … WHERE t.col=t1.col;

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

Примеры:

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

DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;
Для сравнения через подзапрос:

DELETE FROM t1 WHERE id IN (SELECT * FROM (SELECT t1.id FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL) as t_x);
удалить из таблицы дубликаты (строки с одинаковыми значениями поля col) с меньшим id

DELETE t1 FROM t t1 JOIN t t2ON t1.col = t2.col AND t1.id < t2.id;
через подзапрос

DELETE t FROM t LEFT JOIN (SELECT max(id) as id, col FROM t GROUP BY col) t1 USING(id) WHERE t1.id IS NULL;
а вот задача пометить важными статьи авторов, имеющих более 10 публикаций, без подзапроса не решается:

UPDATE articles SET important=1 WHERE author IN (SELECT * FROM (SELECT author FROM articles GROUP BY 1 HAVING count(*)>10) t);
или

UPDATE articles, (SELECT author FROM articles GROUP BY 1 HAVING count(*)>10) t SET important=1 WHERE author = t.author;
в завершение рассмотрим пример, когда подзапрос находится в части SET. Например, для строк с id>10 мы хотим установить значение поля col равное значению этого поля для строки с id равным 2.

UPDATE t as t1 JOIN t as t2 ON t2.id=2 SET t1.col = t2.col WHERE t1.id > 10;
через подзапрос

UPDATE t
SET col = (SELECT * FROM (SELECT col FROM t WHERE id = 2) AS t1)
WHERE id = >10;

Изменение нескольких столбцов в таблице

Синтаксис

Синтаксис ALTER TABLE для редактирования нескольких столбцов в существующей таблице Oracle:

ALTER TABLE table_name
MODIFY (column_1 column_type,
column_2 column_type,
…
column_n column_type);

Пример

Рассмотрим пример, который показывает, как изменить несколько столбцов в таблице Oracle с помощью оператора ALTER TABLE.
Например:

Oracle PL/SQL

ALTER TABLE customers
MODIFY (customer_name varchar2(100) not null,
city varchar2(75));

1
2
3

ALTERTABLEcustomers

MODIFY(customer_namevarchar2(100)notnull,

cityvarchar2(75));

В этом примере ALTER TABLE модифицирует столбцы customer_name и city.

подзапросы lateral

В MySQL 8.0.14 добавлена поддержка зависимых подзапросов в части FROM, с помощью которых наша задача решается оптимальным образом. Сначала формируется список идентификаторов пользователей (производная таблица t1) и для каждого из выбираются нужные строки (коррелированный from-подзапрос t2).

select t2.* from (select user_id from posts group by user_id) as t1,
lateral (select * from posts where t1.user_id=posts.user_id order by date_added desc limit 3) as t2;

К удивлению, при выборе строк в подзапросе t2 сервер читает все строки группы и делает файловую сортировку вместо нахождения 3 нужных строк по уникальному индексу (user_id, date_added). Возможно в будущих версиях это поведение будет исправлено.

На сегодняшний день можно применить следующий трюк, благодаря которому MySQL будет использовать индекс — расширить выражение сортировки следующим образом:

.. order by user_id desc, date_added desc limit 3

Для выборки случайных строк из группы достаточно в lateral подзапросе заменить сортировку на случайную — order by rand().

Обновление значения столбца с выражением

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

Ниже приводится очень простое выражение, где оно увеличивает значение заработной платы на 500 для всех сотрудников отдела IT.

mysql> UPDATE worker SET salary=salary+500 WHERE dept='IT';
Query OK, 3 rows affected (0.01 sec)
Rows matched: 4  Changed: 3  Warnings: 0

Были только 4 записи, которые соответствовали условию выше. Но только три записи были обновлены, как показано ниже, в качестве одного из записей сотрудников, который принадлежит к техническому отделу, имел нулевое значение в поле заработной платы. Таким образом, зарплата выше + 500 выражение по прежнему NULL, и не обновляется эта конкретную запись.

mysql> SELECT * FROM worker;
+-----+--------+------------+--------+
| id  | name   | dept       | salary |
+-----+--------+------------+--------+
| 100 | Andrey | IT         |   5500 |
| 200 | Anton  | IT         |   6000 |
| 300 | Maxim  | Marketing  |   7000 |
| 400 | Dimon  | Marketing  |   9500 |
| 500 | Anton  | IT         |   6500 |
| 501 | Anna   | IT         |   NULL |
+-----+--------+------------+--------+

Обновление только выбранных строк

Вместо того, чтобы обновить все записи, вы можете выборочно обновить определенные записи на основе условия WHERE.

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

mysql> UPDATE worker SET dept='Marketing' WHERE salary >=7000;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

Были только две записи, которые соответствовали выше, таблица обновилась, как показано ниже.

mysql> SELECT * FROM worker;
+-----+--------+------------+--------+
| id  | name   | dept       | salary |
+-----+--------+------------+--------+
| 100 | Andrey | IT         |   5000 |
| 200 | Anton  | IT         |   5500 |
| 300 | Maxim  | Marketing  |   7000 |
| 400 | Dimon  | Marketing  |   9500 |
| 500 | Anton  | IT         |   6000 |
| 501 | Anna   | IT         |   NULL |
+-----+--------+------------+--------+

Мы обсуждали много различных практических условий WHERE в нашем учебнике MySQL по команде SELECT. Это очень полезно, чтобы понять, как использовать предложение WHERE эффективно во время UPDATE: 25 основных примеров в MySQL для команды SELECT.

Пример использования условия EXISTS

Вы также можете выполнять более сложные обновления в Oracle.

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

Например:

Oracle PL/SQL

UPDATE suppliers
SET supplier_name = (SELECT customers.customer_name
FROM customers
WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS (SELECT customers.customer_name
FROM customers
WHERE customers.customer_id = suppliers.supplier_id);

1
2
3
4
5
6
7

UPDATEsuppliers

SETsupplier_name=(SELECTcustomers.customer_name

FROMcustomers

WHEREcustomers.customer_id=suppliers.supplier_id)

WHEREEXISTS(SELECTcustomers.customer_name

FROMcustomers

WHEREcustomers.customer_id=suppliers.supplier_id);

В этом Oracle примере UPDATE, всякий раз, когда supplier_id соответствует значение customer_id, то supplier_name будет перезаписано в customer_name из таблицы customers.

Вывод данных из нескольких таблиц

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

Например мы имеем таблицу с книгами — books и их заказами — orders.

Выведем колонки id, book_id и status для таблицы orders.

Имея идентификаторы записей таблицы books в колонке book_id, мы можем соотнести их с колонкой id в таблице books с помощью команды . В результате мы можем узнать статус заказа для каждой книги.

Выведем колонки title, author, price из таблицы books и колонку status из таблицы orders.

Вывести колонки с одинаковыми именами, например id, в данном случае не получится, т.к. они имеются в обоих таблицах.