Бывает так, что подзапрос одиночной строки возвращает более одной строки. В таком случае возникнет ошибка.
Для каждого магазина найдем одного сотрудника с должностью 'MANAGER'
.
SELECT s.store_id,
s.name,
(SELECT e.employee_id
FROM employee e
WHERE e.store_id = s.store_id
AND e.rank_id = 'MANAGER'
) AS employee_id
FROM store s
ORDER BY s.store_id
error: more than one row returned by a subquery used as an expression
Посмотрим, что там с данными не так?
SELECT e.store_id,
e.rank_id,
e.last_name || ' ' || e.first_name AS full_name
FROM employee e
WHERE e.rank_id = 'MANAGER'
ORDER BY e.store_id, e.last_name, e.first_name
В магазине 201 два менеджера, а в магазине 600 — три.
В такой ситуации есть два варианта развития событий. Мы можем решать неправильную задачу. Если в магазине может быть несколько менеджеров, то мы должны работать с массивом менеджеров. В таком случае может быть приемлемым получить одного из них, например, первого по алфавиту.
Возможно, менеджер в магазине должен быть только один. Просто кто-то не создал уникальный ключ, и пользователи создали несколько менеджеров в магазине. В таком случае, мы написали правильный запрос и необходимо избавляться от неправильных данных и дорабатывать структуру данных.
В любом случае, необходимо гарантировать, что в результате подзапроса будет возвращено не более одной строки. Для этого необходимо одно из:
- должен быть уникальный ключ, гарантирующий, что в результате подзапроса будет не более одной строки;
- использовать агрегатную функцию;
- использовать
LIMIT 1
для ограничения количества строк.
Воспользуемся LIMIT 1
:
SELECT s.store_id,
s.name,
(SELECT e.employee_id
FROM employee e
WHERE e.store_id = s.store_id
AND e.rank_id = 'MANAGER'
ORDER BY e.last_name,
e.first_name,
e.middle_name
LIMIT 1
) AS employee_id
FROM store s
ORDER BY s.store_id
P.S. Если нам нужен список ФИО, то можно воспользоваться string_agg
:
SELECT s.store_id,
s.name,
(SELECT string_agg (
e.last_name || ' ' || e.first_name, '; '
ORDER BY e.last_name,
e.first_name
)
FROM employee e
WHERE e.store_id = s.store_id
AND e.rank_id = 'MANAGER'
) AS employees
FROM store s
ORDER BY s.store_id
Таких функций в PostgreSQL довольно много, и они заслуживают отдельной темы.
Technically, to remove the error, add LIMIT 1
to the subquery to return at most 1 row. The statement would still be nonsense.
... 'SELECT store_key FROM store LIMIT 1' ...
Practically, you want to match rows somehow instead of picking an arbitrary row from the remote table store
to update every row of your local table customer
.
I assume a text column match_name
in both tables (UNIQUE
in store
) for the sake of this example:
... 'SELECT store_key FROM store WHERE match_name = ' || quote_literal(customer.match_name) ...
But that’s an extremely expensive way of doing things.
Ideally, you completely rewrite the statement.
UPDATE customer c
SET customer_id = s.store_key
FROM dblink('port=5432, dbname=SERVER1 user=postgres password=309245'
, 'SELECT match_name, store_key FROM store')
AS s(match_name text, store_key integer)
WHERE c.match_name = s.match_name
AND c.customer_id IS DISTINCT FROM s.store_key;
This remedies a number of problems in your original statement.
Obviously, the basic error is fixed.
It’s typically better to join in additional relations in the FROM
clause of an UPDATE
statement than to run correlated subqueries for every individual row.
When using dblink, the above becomes a thousand times more important. You do not want to call dblink()
for every single row, that’s extremely expensive. Call it once to retrieve all rows you need.
With correlated subqueries, if no row is found in the subquery, the column gets updated to NULL, which is almost always not what you want. In my updated query, the row only gets updated if a matching row is found. Else, the row is not touched.
Normally, you wouldn’t want to update rows, when nothing actually changes. That’s expensively doing nothing (but still produces dead rows). The last expression in the WHERE
clause prevents such empty updates:
AND c.customer_id IS DISTINCT FROM sub.store_key
Related:
- How do I (or can I) SELECT DISTINCT on multiple columns?
Есть небольшая база данных, в ней кусок из трех таблиц
CREATE TABLE students
(
student_id SERIAL PRIMARY KEY,
first_name CHARACTER VARYING(30),
last_name CHARACTER VARYING(30)
);
CREATE TABLE courses
(
course_id SERIAL PRIMARY KEY,
course_name CHARACTER VARYING(30),
);
CREATE TABLE students_courses
(
student_course_id SERIAL PRIMARY KEY,
student_id INTEGER REFERENCES students (student_id),
course_id INTEGER REFERENCES courses (course_id)
);
Мне нужно выводить всех студентов, у которых есть введенный мною курс.
Я пытался сделать это через тройной подзапрос:
SELECT first_name, last_name
FROM students
WHERE student_id = (SELECT student_id
FROM students_courses
WHERE course_id = (SELECT course_id
FROM courses
WHERE course_name = 'Math'));
Но мне выдает «ОШИБКА: подзапрос в выражении вернул больше одной строки».
Это происходит уже при выполнении самого «верхнего» запроса, так как при выполнении только 2 внутренних я получаю таблицу с айдишниками студентов, как на скрине
Я понимаю, что проблема в том, что подзапрос возвращает не одно значение, а сразу несколько, но я не могу понять, как можно по другому получить имена студентов.
P.S. Я также понимаю, что проще сделать это через код, занести айди студентов в резалтСет и пройтись по нему, подбирая имена, но хотелось бы понять, как это можно сделать имея лишь консоль СУБД под рукой
Как правильно делать подзапрос?
ERROR: ОШИБКА: подзапрос в выражении вернул больше одной строки
Limit 1 не нужен
select (select name from citi where zip_code = t.ship ) from town t
-
Вопрос заданболее двух лет назад
-
791 просмотр
3
комментария
Пригласить эксперта
Ответы на вопрос 1
(LEFT) JOIN же
Если больше одной строки хочется вернуть — возвращать их массивом JSON’ов, например
Комментировать
Похожие вопросы
-
Показать ещё
Загружается…
14 июн. 2023, в 01:02
5000 руб./за проект
13 июн. 2023, в 23:37
1000 руб./в час
13 июн. 2023, в 23:22
15000 руб./за проект
Минуточку внимания
41 / 32 / 12 Регистрация: 15.04.2013 Сообщений: 365 |
|
1 |
|
Ошибка: подзапрос выражения вернул больше одной строки08.06.2022, 16:35. Показов 843. Ответов 10
Всем здравствуйте! Помогите пожалуйста разобраться. Только начали с этим работать. Необходимо сделать простейшее: есть 2 таблицы Склад и Разработка. Связаны по столбцам Обозначение. В Разработку забирают какое то количество Обозначения и в таблице Склад надо это отразить. При этом проверяется условие, чтобы Статус в таблице разработки стоял равным 0 и Количество_нужно было не больше Количество на Складе . При выполнении скрипта выходит ошибка: подзапрос выражения вернул больше одной строки Как это исправить? Т.е. скрипт должен просто пересчитать количество на складе, в зависимости от того, сколько взяли в разработку
0 |
Programming Эксперт 94731 / 64177 / 26122 Регистрация: 12.04.2006 Сообщений: 116,782 |
08.06.2022, 16:35 |
10 |
Модератор 4206 / 3046 / 581 Регистрация: 21.01.2011 Сообщений: 13,190 |
|
08.06.2022, 16:45 |
2 |
Во-первых, подзапрос выглядит ужасно. Почитай про JOIN.
1 |
41 / 32 / 12 Регистрация: 15.04.2013 Сообщений: 365 |
|
08.06.2022, 17:00 [ТС] |
3 |
Grossmeister, я это вижу в первый раз в жизни. Парень-практикант пришел с вопросом, сидим разбираемся. Про IN мы видели упоминания, но не поняли как это использовать Вот здесь Установить стоимость контрактов, начинающихся после даты, следующей за датой выполнения запроса, в размере, на похожая тема, там нет IN и нет JOIN, но у нас так не работает Добавлено через 11 минут
0 |
1607 / 1116 / 165 Регистрация: 23.07.2010 Сообщений: 6,476 |
|
08.06.2022, 18:02 |
4 |
Grossmeister, прочитала про JOIN: зачем он нужен в данном случае? Нам не надо объединять 2 таблицы. еще один адепт `зачем join, если связей я не вижу` Добавлено через 3 минуты Добавлено через 1 минуту Добавлено через 2 минуты
0 |
41 / 32 / 12 Регистрация: 15.04.2013 Сообщений: 365 |
|
08.06.2022, 18:15 [ТС] |
5 |
pincet, а можно просто человеческим языком ответить, а не ерничать? Русским языком написано: впервые вижу.
0 |
1607 / 1116 / 165 Регистрация: 23.07.2010 Сообщений: 6,476 |
|
08.06.2022, 18:22 |
6 |
Aline_u, простым человеческим языком — никуда не годится запрос от слова совсем. и не дай бг такое в прод впилить. а читать лекцию почему — нету времени)
0 |
Нарушающий 416 / 304 / 46 Регистрация: 13.04.2022 Сообщений: 1,754 |
|
08.06.2022, 18:48 |
7 |
Aline_u, вы видите первую ошибку, т.к. сервер останавливается после первой. Найдите кого-то с знанием SQL, или обстоятельно опишите что вы хотите осуществить, прилагая структуры таблиц.
0 |
41 / 32 / 12 Регистрация: 15.04.2013 Сообщений: 365 |
|
08.06.2022, 18:53 [ТС] |
8 |
QueryMonkey, скрипт должен просто пересчитать количество на складе, в зависимости от того, сколько взяли в разработку
0 |
Нарушающий 416 / 304 / 46 Регистрация: 13.04.2022 Сообщений: 1,754 |
|
08.06.2022, 19:03 |
9 |
Aline_u, «для каждого обозначения товара, уменьшить количество единиц на складе на количество единиц взятых в разработку со статусом 0». Так?
0 |
41 / 32 / 12 Регистрация: 15.04.2013 Сообщений: 365 |
|
08.06.2022, 19:05 [ТС] |
10 |
QueryMonkey, да, именно так
0 |
QueryMonkey Нарушающий 416 / 304 / 46 Регистрация: 13.04.2022 Сообщений: 1,754 |
||||
08.06.2022, 19:13 |
11 |
|||
1 |
IT_Exp Эксперт 87844 / 49110 / 22898 Регистрация: 17.06.2006 Сообщений: 92,604 |
08.06.2022, 19:13 |
Помогаю со студенческими работами здесь Как считать строки из файла, если там больше одной строки? Запрос вернул больше одного значения Вложенный запрос вернул больше… Вложенный запрос вернул больше одного значения Вложенный запрос вернул больше одного значения Вложенный запрос вернул больше одного значения Искать еще темы с ответами Или воспользуйтесь поиском по форуму: 11 |