74594

UPDATE or DELETE first then INSERT

Question:

I'm asking this again, because I can't get a straight answer from other questions on SO.

My situation is as follows, two tables, phones and features.

Structure:

<strong>phones</strong>

<ul><li>id </li> <li>brand_id </li> <li>model </li> <li>picture </li> <li>published</li> </ul>

<strong>features</strong>

<ul><li>id </li> <li>phone_id </li> <li>feature</li> </ul>

Now, features contains a whole lot of values, which only between 1 & 4 will correspond to a certain phone via phone_id.

Now, when I <em>update</em> features, do you think <strong>deleting</strong> and then <strong>inserting</strong> new features again or <strong>updating</strong> existing features will be best?

// edit:

<strong>specials</strong>

<ul><li>id* </li> <li>phone_id </li> <li>contract_id </li> <li>start_publish </li> <li>finish_publish </li> <li>published</li> </ul>

<strong>contract</strong>

<ul><li>id*</li> <li>name</li> <li>benefit </li> <li>duration </li> <li>price, etc.</li> </ul>

<strong>brand</strong>

<ul><li>id</li> <li>name</li> <li>logo</li> </ul>

So basically, a special links a phone and a contract together, and grabs info from both tables for output. Then during that process, each phone's features get loaded separately using a function.

// edit 2:

Oh, and only <strong>one</strong> phone's features will be deleted/updated at a time, and also, only between 1-4 features will be deleted at a time. Also, features can always be more or less, and never a set number.

Answer1:

If the number of features per phone will ever change, then you must delete and insert. To update, you'd need to:

<ul><li>get the current set of features</li> <li>figure out what is different, what was added, and what was removed</li> <li>run queries to update, insert new data, and delete old data.</li> </ul>

Where as if you just delete the whole list and re-insert them, you save yourself some maintenance headaches.

If consistency of your data is an issue or concern (the data is deleted but an error happens before insert causing the script to not complete) then wrap your queries in a transaction.

Take this example:

I have a blog with Posts(id, title, body) and Categories(id, post_id, category). My first Post, "Hello World" is categorized under "meta, php, and javascript". Then a year later, I revise the post to have some information about mysql (or I add a mysql category) and take out javascript.

Here's what I'd need to do (mostly psuedocode):

//old set SELECT * FROM Categories WHERE post_id=1 foreach($categories as $category) { if(in_array($category['id'], $_POST['categories']) { //this category is in the new set and the old set, do nothing } elseif(!in_array($category['id'], $_POST['categories']) { //this category is in the old set, but not the new one, delete it $queries[] = "DELETE FROM Categories WHERE id=".$category['id']; } else { //new category, add it $queries[] = "INSERT INTO Categories()..." } } foreach($queries as $item) { //run query }

Versus this:

DELETE FROM Categories WHERE post_id=$_POST['id'] foreach($_POST['categories'] as $item) { INSERT INTO Categories() ... }

Personally, I think option #2 is better.

Answer2:

When you "update features" you do just that, update.

Answer3:

UPDATE should be used. Alternatively, deleting and inserting can be done in one REPLACE command.

<a href="http://dev.mysql.com/doc/refman/5.0/en/replace.html" rel="nofollow">http://dev.mysql.com/doc/refman/5.0/en/replace.html</a>

Also, if features can be shared (many to many relationship), you may want to consider a third table that only links phone ids to feature ids.

Recommend

  • Visual Studio 2012: exclude compiled file from linking
  • WCF WebService - Is there a way to determine that client received response?
  • how to pivot complex dataframe
  • Android VideoView plays 2 videos at the same time
  • Subclassing a Pandas DataFrame, updates?
  • How to include Web reference endpoint configuration in another project
  • Measure heap used by each object in Java [closed]
  • Einsum optimize fails for basic operation
  • Implicit property animations do not work with CAReplicatorLayer?
  • Can't find cygwin setup.exe file?
  • jQueryUI dialog replacement for confirm?
  • Microsoft Chart Controls for Microsoft .NET Framework 4.0
  • CUDA NSight is not installed with CUDA 5.0 installation file on Windows 8? [closed]
  • Show records ordered with maximum price first in PHP & MySQL
  • What version of Java should I use with Cassandra 2.0?
  • Is mp4 stream able with ffserver?
  • Are there “Dynamic Playlists” of unit tests in Visual Studio?
  • Appium MobileElement swipe returns unknown server error
  • Python/Javascript: WYSIWYG html editor - Handle large documents fast and/or design theory
  • IE10 strips out hashtag from the URL
  • Find JSON nested nodes using multiple string values
  • New Firebase failed: First argument must be a valid firebase URL and the path can't contain “.”
  • Hector: how to query parts of a Composite Type
  • JSON encode and decode on PHP
  • Building Qt project for C++11 standard
  • Cannot upload to OneDrive using the new SDK
  • converting text file into xml using php?
  • How can I sort a a table with VBA with given text condition?
  • Ajax Loaded meta Tags
  • How to rebase a series of branches?
  • Deserializing XML into class C#
  • Function pointer “assignment from incompatible pointer type” only when using vararg ellipsis
  • File upload with ng-file-upload throwing error
  • Free memory of cv::Mat loaded using FileStorage API
  • Understanding cpu registers
  • How do I configure my settings file to work with unit tests?
  • Java static initializers and reflection
  • python draw pie shapes with colour filled
  • How to Embed XSL into XML
  • How do I use LINQ to get all the Items that have a particular SubItem?