Add to value if exists or insert if doesn't

Databases
1

I have a temporary table called product_search_table with 2 fields, id and score.

I want to insert values, from a search on another table. I have this query that does so:

INSERT INTO product_search_table (id, score)
			SELECT cart_product.product_id as id, 
			MATCH (
				product_name, product_description
			)
			AGAINST (
				"'.$search_str.'"
			) AS score
			FROM cart_product
			WHERE product_available = "yes"
			HAVING score >0

This works, however if product_search_table already has an id with a score in it, then it gets duplicates the id with 2 seperate scores. I would rather not duplicate the id and just add to the score. And only insert if the id does not already been inserted.

Make sense?

2

Thank you!!

3

HAVING new_score > 0
ON DUPLICATE KEY UPDATE score = score + VALUES(score)

4

Thanks… I looked up using ON DUPLICATE KEY UPDATE

Is this right:

INSERT INTO product_search_table (id, score)
SELECT cart_product.product_id, 
MATCH (
    product_name, product_description
)
AGAINST (
    "'.$search_str.'"
) AS new_score
FROM cart_product
WHERE product_available = "yes"
HAVING score > 0
ON DUPLICATE KEY UPDATE score = score + new_score

Thanks.

5

use the ON DUPLICATE KEY UPDATE option of the INSERT statement