MySQL INSERT/UPDATE without ON DUPLICATE KEY

Question

I may have either misunderstood how to use the ON DUPLICATE KEY syntax, alternatively my database structure needs some work but here goes.

I have a table (bookings-meta) which represents meta data associated with another table (bookings) , different rows in the bookings table may or may not have specific meta data associated with them in the other table.

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

From my understanding, to use ON DUPLICATE KEY I need to know what in this case is the meta_id, often this isn't the case, I'm trying to simply push a key, value pair to the table using the booking_id, so if the particular key exists then its replaced otherwise its inserted.

At the moment I have a seperate query to try to select the row, if its found then I UPDATE, if not then its an INSERT.

Is there a way of doing an insert/update in one query without using ON DUPLICATE KEY or have I missed a trick with this one?

Answer 1

If possible, I'd drop the meta_id column entirely and turn booking_id and key into a composite primary key. That'll save space in your table, allow use of ON DUPLICATE KEY, and be cleaner in general.