#mysql #sql
#mysql #sql
Вопрос:
У меня есть две таблицы products и filters. Моя таблица продуктов выглядит следующим образом:-
id name color_id size_id composition_id
1 Test Black 60x60 CM Cotton
2 Test2 Red 60X90 CM Acryllic
Моя таблица фильтров, как показано ниже:-
id filter_name
1 Black
2 Red
22 60x60 CM
23 60X90 CM
61 Cotton
62 Acryllic
Мой ожидаемый результат, как показано ниже:-
id name color_id size_id composition_id
1 Test 1 22 61
2 Test2 2 23 62
Я попробовал приведенный ниже запрос, который работает только для color_id. Запрос приведен ниже:-
UPDATE products
INNER JOIN filters ON products.color_id = filters.filter_name
SET products.color_id = filters.id
Я хочу обновить все ‘color_id, size_id, composition_id’ в одной инструкции update. Возможно, нам нужно использовать операторы Case. Кто-нибудь может помочь?
Ответ №1:
Один из способов, которым вы можете это сделать, — это набор коррелированных подзапросов, по одному для каждого столбца, который вам нужно просмотреть. Мы используем COALESCE
, чтобы, если значение не найдено, сохранить предыдущее значение:
UPDATE products p
SET color_id = COALESCE((SELECT id FROM filters WHERE filter_name = p.color_id), color_id),
size_id = COALESCE((SELECT id FROM filters WHERE filter_name = p.size_id), size_id),
composition_id = COALESCE((SELECT id FROM filters WHERE filter_name = p.composition_id), composition_id)
Затем вы можете SELECT * FROM products
:
id name color_id size_id composition_id
1 Test 1 22 61
2 Test2 2 23 62
Вы также можете достичь того же результата с помощью UPDATE ... JOIN
запроса, который может быть более эффективным:
UPDATE products p
LEFT JOIN filters f1 ON f1.filter_name = p.color_id
LEFT JOIN filters f2 ON f2.filter_name = p.size_id
LEFT JOIN filters f3 ON f3.filter_name = p.composition_id
SET color_id = COALESCE(f1.id, color_id),
size_id = COALESCE(f2.id, size_id),
composition_id = COALESCE(f3.id, composition_id)
Комментарии:
1. Ваш способ правильный, но что, если мои 100 записей уже обновлены, и я снова запускаю этот запрос … более 100 записей будут пустыми.. Надеюсь, вы понимаете
2. @kunal Я не понимал, что вы можете запускать запрос несколько раз. Я обновил его (и демо) для этой ситуации
3. на самом деле мой клиент не хочет заполнять идентификаторы в столбце.. Итак, мой клиент каждую неделю предоставляет таблицу csv с именами, поэтому я буду обновлять sql-запрос вручную. Надеюсь, вы понимаете
4. @kunal Я не могу сказать, что виню их — гораздо проще писать слова! Но новые запросы, которые я опубликовал, должны делать то, что вы хотите.
5.
COALESCE
просматривает каждый из ее аргументов, начиная с левого, и возвращает первый, которого нетNULL
. Так, например, во втором запросе, если нет совпадения дляcolor_id
infilters
,f1.id
будетNULL
иCOALESCE(f1.id, color_id)
вернет исходное значениеcolor_id