Mysql – Transform SELECT query to DELETE to remove extra taxonothe entries

deleteMySQLmysql-5.7

I try this query shows me the products that have duplicated, term_taxonomy_id in my web corresponds the stock of the product
(none, medium, high, low)

I would like to transform it into one query DELETE… that will delete those that have repeated and leave only the most current by the term_order, because if the product have two stock tag or more is not displayed on the web.

They only have to have a tag

  • object_id=1 have high,none
  • object_id=3 have none, medium*

table: wp_term_relationships

+-----------+------------------+-----------+
| object_id | term_taxonomy_id | term_order|
+-----------+------------------+-----------+
| 1         | high             | 0         |
| 1         | none             | 1         |<---delete none
| 1         | bbb              | 10        |
| 1         | ccc              | 10        |
| 2         | high             | 0         |
| 2         | aaa              | 11        |
| 2         | bbb              | 11        |
| 2         | ccc              | 11        |
| 3         | none             | 0         |
| 3         | medium           | 1         |<---delete medium
| 3         | high             | 12        |<---delete high
| 4         | jjj              | 12        |
| 5         | kkk              | 12        |
| 5         | lll              | 12        |
| 5         | high             | 12        |
| *         | *                | *         |
+-----------+------------------+-----------+

SELECT object_id,term_taxonomy_id,term_order 
FROM wp_term_relationships 
WHERE (    term_taxonomy_id = none
        OR term_taxonomy_id = medium
        OR term_taxonomy_id = high
        OR term_taxonomy_id = low)
GROUP BY object_id
HAVING COUNT(*)>1

It can only have one type of taxonomy related to stock, but there can be many more taxonomies. That's why I put aaa,bbb,ccc,zzz the only exclusive ones are those related to high,medium,low,none stock.

I want to delete all the records related to the stock (high,medium,low,none) just leave one for the lowest order of completion and leave all the items values that are not related to the stock.

My platform is MySQL 5.7 (can't use except)

Best Answer

If I unserstand the task correctly:

  1. Delete "wrong" records
DELETE t1.*
FROM wp_term_relationships t1
WHERE t1.term_taxonomy_id IN (`none`,'low','medium','high')
  AND EXISTS ( SELECT NULL
               FROM wp_term_relationships t2
               WHERE t2.term_taxonomy_id IN ('none','low','medium','high')
                 AND t1.object_id = t2.object_id 
                 AND t1.term_order > t2.term_order )
  1. Forbid "wrong" records insertion:
ALTER TABLE wp_term_relationships 
ADD COLUMN term_taxonomy_check VARCHAR(8) 
           AS (CASE WHEN term_taxonomy_id IN (`none`,'low','medium','high')
                    THEN term_taxonomy_id END),
UNIQUE INDEX idx_term_taxonomy_check (object_id, term_taxonomy_check);
Related Question