Best way to update MetaData Insert/Update if exists [MySQL]-CodePudding

I have one table of Jobs and another containing metadata about that job referencing the ID:

`jobs`
id          int; primary, unique
description varchar 
location    varchar 
salary      varchar 

`metadata`
id                 int; primary, unique, auto-increment
job_id             int
metadata_key       varchar  (ex. date updated)
metadata_value     varchar  (ex. 10-26/22)

Each job_id will have multiple rows of metadata describing more about the job contained in metadata_key and metadata_value

Here's a look at the metadata table:

meta_id     job_id     meta_key     meta_value  

13091      2144207     featured      1   

13090      2144223     featured      0

13088      2144207     featured      0

13087      2144223     featured      0

13084      2144223     expire        10-30-22

13082      2144223     salary_to     250000

13081      2144223     salary_from   200000

13080      2144223     ft_expiry     10-30-22

13079      2144223     ft_date       10-26-22

What is the best way (through query, not stored procedure) to Insert a metadata record if it doesn't exist such as adding a 'featured' record or Updating if the key already exists for a given job_id?

ON DUPLICATE KEY will not work as the Unique key for metadata is the id, I need to determine:

for a given job_id (not Unique)
check meta_key value and insert if it doesn't exist, update if it does exist

CodePudding user response：

Define a UNIQUE KEY over the pair of columns (job_id, meta_key).

Then you can use INSERT...ON DUPLICATE KEY UPDATE.

You could even make those two columns the primary key, since you don't really need an auto-increment integer primary key in a table like this.