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?

ceejayoz · Accepted Answer · 2009-07-28 20:30:23Z

6

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.

edited Jul 28, 2009 at 20:30

answered Jul 28, 2009 at 20:12

ceejayoz

181k42 gold badges311 silver badges382 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Duncan Over a year ago

Thanks, that's perfect, I don't know why but I kept on telling myself I needed that meta_id, on reflection its used nowhere!

Collectives™ on Stack Overflow

MySQL INSERT/UPDATE without ON DUPLICATE KEY

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related