CODEWITHSUNDEEP
phpmysql
Kirill
Kirill

Reputation: 308

if value exists UPDATE else INSERT * with 2 non uniques

I've read through the other answers to similar questions, but wasn't able to understand how to tackle my issue.

The table that I'm working on contains meta keys for "jobs", the meta keys and the id of the "job" repeat many times.

Table Structure:

id | job_id | meta_name | meta_content

id is unique, job_id will repeat 3-10 times with different meta_name and meta_content values, meta_name must only exist once per job_id, meta_content should not be relied on as it will store anything from text to email to phone numbers.

All my attempts at using ON DUPLICATE KEY UPDATE fail and just create new rows, resulting in multiple job_id+meta_name combinations.

Can anyone help with building a query?

Upvotes: 2

Views: 79

Answers (2)

C3roe
C3roe

Reputation: 96306

meta_name must only exist once per job_id

Then you should create a combined UNIQUE index on those two columns.

With that, INSERT … ON DUPLICATE KEY UPDATE will work as intended for such combinations of data.

Upvotes: 1

Danyou
Danyou

Reputation: 190

You have to combine two unique key columns with

UNIQUE KEY keyname (job_id,meta_name)

Upvotes: 0

Related Questions