Mysql - Table updated through a query. On duplicate key update not working

Question

The task is to keep "mg_brands" table updated with values coming from query on another two tables.

Tables are:

mg_brands | mg_term_taxonomy  |mg_terms
----------|-------------------|--------------
term_id   | term_taxonomy_id  |term_id
name      | term_id           |name
          | taxonomies        |

Basically, it works in this way: mg_terms contain records having different categories identified by the "field mg_term_taxonomy.taxonomies".

I would like to extract records having taxonomy "brand" and transfer that data into "mg_brands". The query should be capable of checking existing records updating them or insert new ones if not exist.

For that I am using the below reported query but it create new records, duplicating them instead of doing what I already said.

INSERT into mg_brands

    SELECT mg_terms.term_id,  mg_terms.name
    FROM mg_terms
    JOIN mg_term_taxonomy
    ON mg_term_taxonomy.term_id=mg_terms.term_id
    WHERE mg_term_taxonomy.taxonomy="product_brand"

    ON DUPLICATE KEY UPDATE
    mg_brands.term_id = mg_terms.term_id,
    mg_brands.name= mg_terms.name

Any suggestion why the ON DUPLICATE KEY UPDATE is not doing its work?

Thanks

What do you mean with "is not doing its work?" Do you get an error? — nacho
– nacho, Commented Aug 7, 2017 at 17:03
Hi, I mean that instead of checking if the record exists and eventually update the fields, the query add records every time I launch it. I suppose it depends from the bad coding on that part. — Alex
– Alex, Commented Aug 7, 2017 at 17:12
A question about ON DUPLICATE KEY that doesn't include the KEYs that the (key)word KEY in that command is referring to (your indexes on that table) is missing a very important part. So please add them (e.g. as output of show create table mg_brands). — Solarflare
– Solarflare, Commented Aug 7, 2017 at 17:14
@Solarflare, the key is the term_id in the mg_brands. At this point I have no idea how to implement the function I need. Any suggestion? — Alex
– Alex, Commented Aug 7, 2017 at 17:18
Then as @Solarflare says, you have a problem with your KEY. Are tyou sure it´s a unique KEY?? It looks like don´t — nacho
– nacho, Commented Aug 7, 2017 at 17:19

nacho · Accepted Answer · 2017-08-07 17:40:52Z

1

You need to set the term_id as UNIQUE KEY so the ON DUPLICATE will work. The problem it´s that now you don´t have any duplicate because you don´t have term_id as PRIMARY KEY

answered Aug 7, 2017 at 17:40

nacho

5,3772 gold badges28 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alex Over a year ago

SOLVED - Indeed I was missing the UNIQUE KEY. As I selected it for the term_id, the query worked fine. Thanks for your help!

Collectives™ on Stack Overflow

Mysql - Table updated through a query. On duplicate key update not working

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related