Ошибка MySQL 1242 — подзапрос возвращает более 1 строки
у меня есть две таблицы в БД со следующей структурой:
Таблица 1: 3 строки — category_id, product_id и position
таблица 2: 3 строки — category_id, product_id и position
я пытаюсь установить положение таблицы 1 в положение таблицы 2, где категория и идентификатор продукта совпадают с таблицами.
ниже SQL, я пытался сделать это, но возвращает ошибку MySQL 1242 — подзапрос возвращает более 1 строки
UPDATE table1
SET position = (
SELECT position
FROM table2
WHERE table1.product_id = table2.product_id AND table1.category_id = table2.category_id
)
Решение
Решение очень простое и может быть сделано в два простых шага. Первый шаг — это предварительный просмотр того, что будет изменено, чтобы избежать уничтожения данных. Это можно пропустить, если вы уверены в своем WHERE пункт.
Шаг 1: предварительный просмотр изменений
Объедините таблицы, используя поля, которые вы хотите сопоставить, выберите все для визуальной проверки соответствия.
SELECT t1.*, t2.*
FROM table1 t1
INNER JOIN table2 t2
ON t1.category_id = t2.category_id
AND t1.product_id = t2.product_id
Вы также можете добавить WHERE предложение, если только некоторые строки должны быть изменены.
Шаг 2: сделать актуальное обновление
Заменить SELECT пункт и FROM ключевое слово с UPDATE, добавить SET пункт, где это принадлежит. Держать WHERE пункт:
UPDATE table1 t1
INNER JOIN table2 t2
ON t1.category_id = t2.category_id
AND t1.product_id = t2.product_id
SET t1.position = t2.position
Это все.
Технические соображения
Индексы столбцов, используемых на JOIN предложение в обеих таблицах является обязательным, если в таблицах более нескольких сотен строк. Если запрос не имеет WHERE условия, то MySQL будет использовать индексы только для самой большой таблицы. Индексы полей, используемых на WHERE условие ускорит запрос. Prepend EXPLAIN к SELECT запрос, чтобы проверить план выполнения и решить, какие индексы вам нужны.
Можете добавить SORT BY а также LIMIT для дальнейшего сокращения набора измененных строк, используя критерии, которые не могут быть достигнуты с помощью WHERE (например, только самые последние / самые старые 100 строк и т. д.). Поместите их на SELECT сначала запрос, чтобы проверить результат, а затем изменить SELECT в UPDATE как описано.
Конечно, индексы на столбцах, используемых на SORT BY пункт является обязательным.
Другие решения
Вы можете запустить этот запрос, чтобы увидеть, что происходит:
SELECT product_id, category_id, count(*), min(position), max(position)
FROM table2
GROUP BY product_id, category_id
HAVING COUNT(*) > 1;
Это даст вам список product_id, category_id пары, которые появляются несколько раз в table2, Тогда вы можете решить, что делать. Вы хотите произвольное значение position? Является ли значение position всегда одно и то же? Вам нужно починить стол?
Достаточно легко решить конкретную проблему с помощью limit 1 или функция агрегации. Тем не менее, вам может понадобиться исправить данные в таблице. Исправление выглядит так:
UPDATE table1 t1
SET t1.position = (SELECT t2.position
FROM table2 t2
WHERE t2.product_id = t1.product_id AND t2.category_id = t1.category_id
LIMIT 1
);