Помогите создать SQL запрос

[AngelinaLookChic]

Друзья!

Помогите пожалуйста создать запрос SQL для массового присвоения категории для всех товаров.

Проблема в том, что у части товаров данная категория уже присвоена. 

Нужно что бы у всех товаров была категория 64

 

Вторая задача

У всех товаров из таблицы oc_product которые имеют stock_status_id 5 удалить все категории в oc_product_to_category и оставить только категорию 64

 

Змінено 24 березня 2019 користувачем AngelinaLookChic

[100napb]

1 hour ago, AngelinaLookChic said:

Помогите пожалуйста создать запрос SQL для массового присвоения категории для всех товаров.

Здравствуйте! Надеюсь, Вы максимально точно изложили задачу. В любом случае, рекомендую сделать бэкап таблицы product_to_category прежде чем будете выполнять скрипты ниже.

 

1. Действительно, "Проблема в том, что у части товаров данная категория уже присвоена". Из-за этого нельзя просто так взять и всем товарам в таблице product_to_category присвоить одну категорию - ограничение уникальности по первичному ключу не позволит (нельзя, что бы в таблице для одного и того же товара существовало более одной записи вида ид_товара + ид_категории). По этому, начинаем с того, что удалим из таблицы product_to_category все записи об одном и том же товаре, кроме одной.

Spoiler

DELETE p2c
  FROM oc_product_to_category p2c
    JOIN (SELECT
        p2c.product_id,
        p2c.category_id
      FROM oc_product p
        JOIN oc_product_to_category p2c
          ON p.product_id = p2c.product_id
      WHERE p.stock_status_id = 5
      GROUP BY p2c.product_id
      HAVING COUNT(*) > 1) tmp
      ON p2c.product_id = tmp.product_id
      AND p2c.category_id = tmp.category_id

2. Ну дальше, как Вы и просили, присваиваем всем товарам 64 категорию. Правда, мы снова можем нарваться на ограничение уникальности. Но тут без уточнения с Вашей стороны чего-либо пока писать не буду. Вы реально хотите что бы у ВСЕХ товаров в магазине была 64 категория?

Spoiler

UPDATE oc_product_to_category p2c
JOIN oc_product p
  ON p.product_id = p2c.product_id
SET p2c.category_id = 64
WHERE p2c.category_id != 64;

 

 

[AngelinaLookChic]

26 минут назад, 100napb сказал:

Здравствуйте! Надеюсь, Вы максимально точно изложили задачу. В любом случае, рекомендую сделать бэкап таблицы product_to_category прежде чем будете выполнять скрипты ниже.

 

1. Действительно, "Проблема в том, что у части товаров данная категория уже присвоена". Из-за этого нельзя просто так взять и всем товарам в таблице product_to_category присвоить одну категорию - ограничение уникальности по первичному ключу не позволит (нельзя, что бы в таблице для одного и того же товара существовало более одной записи вида ид_товара + ид_категории). По этому, начинаем с того, что удалим из таблицы product_to_category все записи об одном и том же товаре, кроме одной.

  Скрыть контент

DELETE p2c
  FROM oc_product_to_category p2c
    JOIN (SELECT
        p2c.product_id,
        p2c.category_id
      FROM oc_product p
        JOIN oc_product_to_category p2c
          ON p.product_id = p2c.product_id
      WHERE p.stock_status_id = 5
      GROUP BY p2c.product_id
      HAVING COUNT(*) > 1) tmp
      ON p2c.product_id = tmp.product_id
      AND p2c.category_id = tmp.category_id

2. Ну дальше, как Вы и просили, присваиваем всем товарам 64 категорию. Правда, мы снова можем нарваться на ограничение уникальности. Но тут без уточнения с Вашей стороны чего-либо пока писать не буду. Вы реально хотите что бы у ВСЕХ товаров в магазине была 64 категория?

  Скрыть контент

UPDATE oc_product_to_category p2c
JOIN oc_product p
  ON p.product_id = p2c.product_id
SET p2c.category_id = 64
WHERE p2c.category_id != 64;

 

 

 Спасибо что откликнулись. Бекап сделан в обязательном порядке) 

 

Я объясню для чего все это делается. 

 

На сайте реализованы категории таким образом: 

 

Одежда/блузки/диор/блузкадиор1 

Одежда/блузки/шанель/блузкашанель1

 

Так-же, эти блузки размещены в категориях одежда, обежда/блузки, то есть во всех категориях одновременно.

 

Есть товары которые закончились и им установлено значение "нет в наличии" 

 

И что бы их не убирать совсем с сайта (что бы не отключать), но что бы они не мешались среди товаров, через SQL хочется все эти товары отправить в категорию "ВСЕ ТОВАРЫ". В данной категории "разброд и шатания", то есть, лежат все товары, без подкатегорий. (Задумка такая, что бы не терять позиции в поисковиках)

 

По шагам нужно сделать следующее:

У товаров которым в таблице (oc_product) установлено значение "нет в наличии" ( stock_status_id 5) убрать все категории

Далее, этим же товарам присвоить в таблице (oc_product_to_category) категорию 64

 

В итоге что мы получим: 

 

В категории "Все товары" - будут все товары и которые в наличии и которых нет в наличии

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

 

 

 

[chukcha]

Вы сами будете писать запрос?

[AngelinaLookChic]

По идее, вообще всем товарам нужно присвоить (oc_product_to_category) категорию 64

 

А потом удалить у товаров которым в таблице (oc_product) установлено значение "нет в наличии" ( stock_status_id 5) убрать все категории КРОМЕ 64

 

Вот ) 

[AngelinaLookChic]

Только что, chukcha сказал:

Вы сами будете писать запрос?

Да, мною уже поменяно значение stock_status_id с 7 на 5 у всех товаров которые имеют status 0 в этой таблице, то есть были отключены. Раньше того чего не было в наличии просто отключалось. 

Это был простой запрос

Змінено 24 березня 2019 користувачем AngelinaLookChic

[chukcha]

2 минуты назад, AngelinaLookChic сказал:

По идее, вообще всем товарам нужно присвоить (oc_product_to_category) категорию 64

Ну, а в чем проблема?

[AngelinaLookChic]

Только что, chukcha сказал:

Ну, а в чем проблема?

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

[chukcha]

а какие операторы вы знаете?

[100napb]

7 minutes ago, AngelinaLookChic said:

По идее, вообще всем товарам нужно присвоить (oc_product_to_category) категорию 64

 

А потом удалить у товаров которым в таблице (oc_product) установлено значение "нет в наличии" ( stock_status_id 5) убрать все категории КРОМЕ 64

 

Вот ) 

тогда просто во втором скрипте добавьте одно условие - нужный статус наличия товара. И все будет ок. Сначала удалить дубли, затем сделать 64 категорию. Должно сработать

UPDATE oc_product_to_category p2c
JOIN oc_product p
  ON p.product_id = p2c.product_id
SET p2c.category_id = 64
WHERE p.stock_status_id = 5
AND p2c.category_id != 64;

 

[AngelinaLookChic]

Только что, chukcha сказал:

а какие операторы вы знаете?

Можно сказать - никакие.

Сегодня вообще впервые открыта база данных SQL. 

 

[AngelinaLookChic]

Только что, 100napb сказал:

тогда просто во втором скрипте добавьте одно условие - нужный статус наличия товара. И все будет ок. Сначала удалить дубли, затем сделать 64 категорию. Должно сработать

UPDATE oc_product_to_category p2c
JOIN oc_product p
  ON p.product_id = p2c.product_id
SET p2c.category_id = 64
WHERE p.stock_status_id = 5
AND p2c.category_id != 64;

 

Спасибо огромное за участие, сейчас попробую. 

Не просто ленивая копипаста, буду разбираться со скриптом )

 

[AngelinaLookChic]

К сожалению, выпадает ошибка: 

Ошибка

SQL запрос:

 

UPDATE oc_product_to_category p2c
JOIN oc_product p
  ON p.product_id = p2c.product_id
SET p2c.category_id = 64
WHERE p.stock_status_id = 5
AND p2c.category_id != 64

 

Ответ MySQL: 

#1062 - Дублирующаяся запись '238-64' по ключу 'PRIMARY'

[100napb]

1 minute ago, AngelinaLookChic said:

Ответ MySQL: 

#1062 - Дублирующаяся запись '238-64' по ключу 'PRIMARY'

Так... кажется Чукча не зря сомневался.

Ладно хоть бэкапы есть и желание экспериментировать ))

 

Вы первый-то скрипт прогнали прежде чем этот апдейт выполнять? Он должен был дубли убрать.

[AngelinaLookChic]

7 минут назад, 100napb сказал:

Так... кажется Чукча не зря сомневался.

Ладно хоть бэкапы есть и желание экспериментировать ))

 

Вы первый-то скрипт прогнали прежде чем этот апдейт выполнять? Он должен был дубли убрать.

 

УПС ))

что то у меня с базой не того) 

сейчас начну с начала. 

 

 

[AngelinaLookChic]

Та же самая ошибка. При этом, двум товарам назначается категория 64.  

 

НО, первый скрипт сработал, и теперь в категории "ВСЕ ТОВАРЫ"  остались только товары в наличии

 

 

Задача теперь получается такая:

1. Назначить всем товарам которых "нет в наличии" (stock_status_id = 5) категорию (oc_product_to_category = 64)

2. Удалить у всех товаров которых "нет в наличии" (stock_status_id = 5) все категории кроме (oc_product_to_category) = 64

 

Я так понимаю, если сначала не назначить 64 категорию, то удалить все под ноль не получится, нельзя оставить товар без категории. 

 

Змінено 24 березня 2019 користувачем AngelinaLookChic

[100napb]

3 hours ago, AngelinaLookChic said:

Та же самая ошибка. При этом, двум товарам назначается категория 64.  

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

 

Итак, что бы появилось понимание, а не ленивая копипаста.

 

1. Установим 64 категорию для всех товаров, у которых статус наличия = 5 И существует всего одна привязка к категории И эта привязка не к категории 64

Spoiler

UPDATE oc_product_to_category p2c
JOIN oc_product p
  ON p.product_id = p2c.product_id
SET p2c.category_id = 64
WHERE p.stock_status_id = 5
AND p2c.category_id != 64
AND EXISTS (SELECT
    1
  FROM oc_product_to_category
  WHERE product_id = p2c.product_id
  GROUP BY product_id
  HAVING COUNT(*) = 1);

2. Зеленый подзапрос находит все товары в таблице product_to_category, у которых а) статус наличия = 5 б) существует более чем одна привязка к товарной категории. Фиолетовая же часть запроса удаляет привязки к категориям для найденных в подзапросе товаров КРОМЕ тех случаев, когда товарная категория 64. Таким образом, после выполнения скрипта в таблице не должно остаться дублей. Но остаются привязки товаров к не 64 категории

Spoiler

DELETE
  FROM oc_product_to_category
WHERE product_id IN (SELECT
      p2c_sub.product_id
    FROM oc_product p_sub
      JOIN oc_product_to_category p2c_sub
        ON p_sub.product_id = p2c_sub.product_id
    WHERE p_sub.stock_status_id = 5
    GROUP BY p2c_sub.product_id
    HAVING COUNT(*) > 1)
  AND category_id != 64

 

3. В принудительном порядке назначаем всем товарам со статусом наличия = 5 64 категорию.

Spoiler

UPDATE oc_product_to_category p2c
JOIN oc_product p
  ON p.product_id = p2c.product_id
SET p2c.category_id = 64
WHERE p.stock_status_id = 5
AND p2c.category_id != 64

 

 

Змінено 24 березня 2019 користувачем 100napb

[legioner26]

17 минут назад, 100napb сказал:

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

 

Итак, что бы появилось понимание, а не ленивая копипаста.

1. Зеленый подзапрос находит все товары в таблице product_to_category, у которых а) статус наличия = 5 б) существует более чем одна привязка к товарной категории. Фиолетовая же часть запроса удаляет привязки к категориям для найденных в подзапросе товаров КРОМЕ тех случаев, когда товарная категория 64. Таким образом, после выполнения скрипта в таблице не должно остаться дублей. Но остаются привязки товаров к не 64 категории

  Скрыть контент

DELETE
  FROM oc_product_to_category
WHERE product_id IN (SELECT
      p2c_sub.product_id
    FROM oc_product p_sub
      JOIN oc_product_to_category p2c_sub
        ON p_sub.product_id = p2c_sub.product_id
    WHERE p_sub.stock_status_id = 5
    GROUP BY p2c_sub.product_id
    HAVING COUNT(*) > 1)
  AND category_id != 64

 

2. В принудительном порядке назначаем всем товарам со статусом наличия = 5 64 категорию.

  Показать контент

UPDATE oc_product_to_category p2c
JOIN oc_product p
  ON p.product_id = p2c.product_id
SET p2c.category_id = 64
WHERE p.stock_status_id = 5
AND p2c.category_id != 64

 

 

А Вы проверили свой запрос?

[legioner26]

http://prntscr.com/n28t5i

 

 

[legioner26]

Уж лучше так уже

DELETE FROM `oc_product_to_category`
WHERE product_id IN (SELECT product_id FROM `oc_product`  WHERE stock_status_id = 5) AND category_id != 64

 

[100napb]

36 minutes ago, legioner26 said:

А Вы проверили свой запрос?

Эм.. Синтаксис там проверять что ли? Ну так за это я спокоен логика и дополнительные проверки сильно зависят от набора данных. На тестовой базе проверял, конечно, благо есть возможность. Другое дело, что у ТС все может быть намного сложнее, чем на примитивном тесте.

 

На всякий случай отредактировал пост выше, добавив дополнительные условия проверки, что бы учесть больше вариантов.

 

P.S.: вероятно, у нас разные версии\настройки сервера БД. Если не ошибаюсь, за подобные проверки отвечает параметр optimizer_switch = 'derived_merge=off'; Ну или phpmyadmin слишком придирчивый. В любом случае, это не шибко важно... У меня выполняется без проблем, например. На самом деле, это типичная "ошибка", типа, а-та-та, некрасиво так явно читать из той же таблицы, которую меняешь. Обычно решается тем, что бы загнать селект в подзрапрос или убрать его join, что бы оптимизатор мускуля стал считать таблицу из подзапроса временной и перестал ругаться

Змінено 24 березня 2019 користувачем 100napb
добавлен в p.s. ответ @legioner26

[legioner26]

37 минут назад, 100napb сказал:

Обычно решается тем, что бы загнать селект в подзрапрос или убрать его join

Ну да.

[legioner26]

DELETE
  FROM oc_product_to_category
WHERE product_id IN (SELECT * FROM(SELECT
      p2c_sub.product_id
    FROM oc_product p_sub
      JOIN oc_product_to_category p2c_sub
        ON p_sub.product_id = p2c_sub.product_id
    WHERE p_sub.stock_status_id = 5
    GROUP BY p2c_sub.product_id
    HAVING COUNT(*) > 1) as ID)
  AND category_id != 64

Вот так уже не ругается )))

[100napb]

2 minutes ago, legioner26 said:

DELETE
  FROM oc_product_to_category
WHERE product_id IN (SELECT * FROM(SELECT
      p2c_sub.product_id
    FROM oc_product p_sub
      JOIN oc_product_to_category p2c_sub
        ON p_sub.product_id = p2c_sub.product_id
    WHERE p_sub.stock_status_id = 5
    GROUP BY p2c_sub.product_id
    HAVING COUNT(*) > 1) as ID)
  AND category_id != 64

Вот так уже не ругается )))

все верно иногда оптимизатор mysql слишком придирается. норм. это его работа ))

если не ошибаюсь, того же результата можно добиться через предварительное выполнение set session optimizer_switch = 'derived_merge=off';

[legioner26]

7 минут назад, 100napb сказал:

set session optimizer_switch = 'derived_merge=off'

Да, но лучше не делать этого )))