SQL best practice: updating information in a database

[three_sixteen]

So I have a database that has 5 tables with assorted information about a computer. I was wondering what the best practice would be for updating a variable number of tables and or columns at any given time.

I have a table called computerInfo and already have the name of the computer and all of its networking info in the table.

In one scenario I want to update only the IP/Subnet/Gateway

In another scenario I may want to update the DNS servers

Right now I'm doing DELETE * FROM computerInfo WHERE (ComputerName = 'computername') and then just inserting a new row into the table. Is there maybe a better way to update the information in the row without deleting the entire row? Or is just cleaning up everything and inserting a new row maybe the best way to go about it?

 

[Tytalus]

I would suggest reading up on the "UPDATE" statement.

Link

That should get you started on where to go next with your question.

 

[three_sixteen]

I would suggest reading up on the "UPDATE" statement.

Link

That should get you started on where to go next with your question.

I should have mentioned that this is all happening through an interface I made in PHP

I did look into the UPDATE statment, but I wasn't sure if using it would be best practice when you don't know which columns are going to be updated at an given time. I would need to define all of the possible columns that can be updated and build a query at runtime.. with all of the possible/random columns that could be updated it seemed like it would get kind of messy.. I figured using DELETE was just easier since I wasn't really worried about data loss?

 

[Wingy]

Which column they update shouldn't matter. You're essentially updating updating all columns to which ever values appear in your php (I assume a form).

So if your table has 2 columns, FirstName, LastName, you could use the UPDATE statement to set FirstName and LastName to the values in your form. Deleting rows can be tricky business, so I don't like your method too much. If you have other tables that associate to the table you want to update through it's primary key, then you've messed up the other table.

UPDATE is your friend here.

 

[JRS]

One (additional) potential pitfall with a delete-then-insert approach is that you really need to make sure you wrap both those in a single transaction context. Otherwise if there is any kind of error inbetween the two calls, the data can get messed up.

imho, for a "best practice" for a web app is use an update statement and update all fields all the time, with exceptions for certain circumstances. If you try to write a bunch of code to keep track of which fields the user changed, you'll probably introduce a lot of complexity (and bugs) with very little gain or return.

The additional overhead for the actual update statement is minimal whether you are updating one field or twenty on a record (unless additional factors come into play like triggers).

 

[BDV]

delete then insert is going to kill your DB.

Run-time update statements are not that bad once you have a script doing it automatically.

 

[Sgraffite]

I think if you don't know that a row exists, you would want to use an insert into on duplicate key update statement.

Link