MySQL Forums :: Newbie :: Looking up a value in a 'subtable' and inserting it if it doesn't exist


Advanced Search

Looking up a value in a 'subtable' and inserting it if it doesn't exist
Posted by: Dave Kennard ()
Date: April 29, 2009 09:47AM

In my database I have one main table, imageData. Then for columns in imageData that only have a few possible values or the same value will be reoccuring often, I have created separate 'subtables' to hold these values:


Now when I want to insert a row into the imageData table, I have to look up the correct key for each value where the value is stored in a 'subtable'. If the value doesn't already exist, then I'll need to insert it.

I can think of two ways to do this - INSERT IGNORE all the subtable values into the subtables, then when updating the imageData table use subqueries to get the ids for the values stored in subtables. e.g.
INSERT IGNORE INTO cameras SET camera = 'my camera'; etc.
INSERT INTO imageData SET camera = (SELECT id FROM cameras WHERE camera = 'mycamera'), etc.

Or otherwise use a stored procedure that selects the ids of the subtable values and stores them as variables for use when inserting into imageData. If the values didn't exist, it would have to insert them and give the variable the value of LAST_INSERT_ID().

So 2 questions:
1. Is my schema sensible?
2. If it is, what would be the best way to handle this scenario?

Thanks

Dave

Options: ReplyQuote


Subject Written By Posted
Looking up a value in a 'subtable' and inserting it if it doesn't exist Dave Kennard 04/29/2009 09:47AM
Re: Looking up a value in a 'subtable' and inserting it if it doesn't exist Rick James 04/30/2009 11:56PM
Re: Looking up a value in a 'subtable' and inserting it if it doesn't exist Dave Kennard 05/02/2009 04:07AM
Re: Looking up a value in a 'subtable' and inserting it if it doesn't exist Rick James 05/02/2009 12:42PM
Re: Looking up a value in a 'subtable' and inserting it if it doesn't exist Dave Kennard 05/06/2009 08:28AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.