MySQL INSERT / UPDATE without DUPLICATE key

Question

Perhaps I either misunderstood how to use the ON DUPLICATE KEY syntax, since my database structure needs some work, but here.

I have a table (bookings-meta) that represents metadata associated with another table (orders), different rows in the order table may or may not have specific metadata associated with them in another table.

The bookings-meta table has the following columns: meta_id (primary key), book_id, key, and value.

From my understanding, in order to use ON DUPLICATE KEY I need to know what meta_id is in this case, often it is not, I try to just push a key, value pair into a table using booking_id, so if a specific key exists, then it is replaced. otherwise it is inserted.

At the moment, I have a separate request to try to select a row, if it is found, then I UPDATE, if not, then this is INSERT.

Is there a way to do an insert / update in a single query without using ON DUPLICATE KEY or did I miss the trick with this?

Answer 1

If possible, I completely lowered the column meta_idand turned booking_idit keyinto a composite primary key. This will save space in your table, allow use ON DUPLICATE KEYand be cleaner at all.