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

[AngelinaLookChic]

Друзья!

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

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

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

 

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

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

 

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

[100napb]

  В 24.03.2019 в 08:57, AngelinaLookChic сказав:

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

Expand  

Здравствуйте! Надеюсь, Вы максимально точно изложили задачу. В любом случае, рекомендую сделать бэкап таблицы 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;

 

 

[AngelinaLookChic]

  В 24.03.2019 в 10:15, 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;

 

 

Expand  

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

 

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

 

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

 

Одежда/блузки/диор/блузкадиор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]

  В 24.03.2019 в 11:02, chukcha сказав:

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

Expand  

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

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

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

[chukcha]

  В 24.03.2019 в 11:04, AngelinaLookChic сказав:

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

Expand  

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

[AngelinaLookChic]

  В 24.03.2019 в 11:07, chukcha сказав:

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

Expand  

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

[chukcha]

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

[100napb]

  В 24.03.2019 в 11:04, AngelinaLookChic сказав:

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

 

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

 

Вот ) 

Expand  

тогда просто во втором скрипте добавьте одно условие - нужный статус наличия товара. И все будет ок. Сначала удалить дубли, затем сделать 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]

  В 24.03.2019 в 11:12, chukcha сказав:

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

Expand  

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

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

 

[AngelinaLookChic]

  В 24.03.2019 в 11:13, 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;

 

Expand  

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

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

 

[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]

  В 24.03.2019 в 11:20, AngelinaLookChic сказав:

Ответ MySQL: 

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

Expand  

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

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

 

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

[AngelinaLookChic]

  В 24.03.2019 в 11:23, 100napb сказав:

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

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

 

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

Expand  

 

УПС ))

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

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

 

 

[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]

  В 24.03.2019 в 12:31, AngelinaLookChic сказав:

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

Expand  

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

 

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

 

1. Установим 64 категорию для всех товаров, у которых статус наличия = 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
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 категории

  Показати вміст

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 категорию.

  Показати вміст

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]

  В 24.03.2019 в 15:21, 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

 

 

Expand  

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

[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]

  В 24.03.2019 в 15:38, legioner26 сказав:

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

Expand  

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

 

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

 

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

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

[legioner26]

  В 24.03.2019 в 15:59, 100napb сказав:

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

Expand  

Ну да.

[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]

  В 24.03.2019 в 16:48, 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

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

Expand  

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

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

[legioner26]

  В 24.03.2019 в 16:53, 100napb сказав:

set session optimizer_switch = 'derived_merge=off'

Expand  

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