0

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)

0

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);
| improve this answer | |
  • I already tried it and it works fine, I only have a problem if the object_id has the same term_order then I can't find it, in this case I would like to delete all of them so that in the next import it will be updated correctly thanks – beikone Mar 18 at 18:23
  • UPDATE wp_term_relationships SET term_order =1 WHERE term_taxonomy_id =high; UPDATE wp_term_relationships SET term_order =0 WHERE term_taxonomy_id =medium; UPDATE wp_term_relationships SET term_order =2 WHERE term_taxonomy_id =low; UPDATE wp_term_relationships SET term_order =3 WHERE term_taxonomy_id =none; – beikone Mar 18 at 19:09
  • @beikone Adding generated column and unique index must be performed when no "wrong" records in a table. And use one UPDATE with FIND_IN_SET instead of 4 separate UPDATEs. – Akina Mar 19 at 4:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.