Re: SQL Update question


"Robert Morley" <rmorley@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:Ok6q7S3kHHA.4188@xxxxxxxxxxxxxxxxxxxxxxx
Generally, if ONE thing needs updated, you just update everything, even if
the values are the same. It's just easier this way because you've only got
one UPDATE SQL statement.

This only applies if you're using UPDATE statements. If you're using a
rs!Field.Value = MyValue, then the one-update thing isn't really an issue, as
DAO or ADO will figure out what's appropriate. In this case, it would
probably be more desirable to not update if the field hasn't changed.

Two reasons NOT to update everything are 1) if there's some kind of
replication going on (particularly column-level replication) in the database,
in which case updating a field, even if it's to the same value, may trigger
unnecessary replication of the data and potentially cause conflicts; and 2) if
you're using a database that supports triggers, updating a field unnecessarily
may cause the trigger to fire. This may be undesirable if, say, you're
tracking the time a specific field last changed and the field hasn't actually
changed.


Lately, I have not only taken to updating only the fields that change, but to
comparing the values first, so that even if they are edited, it doesn't update
them if the new value is the same as the old value.
It means more cursors on the database server end, since there are lots of
different update statements hitting it, instead of the same ones all the time.
But in my case, the server is bored and idle most of the time anyway.
I am now logging every change in every field, so it is worth it to me to avoid
unnecessary updating whenever possible.


.

Relevant Pages

  • Re: Northwind ReportsTo NULL ComboBox
    ... concerned about SELECT or UPDATE statements. ... although in fact it does come from a database. ... Updating the data source is a separate issue ... > Values" in SQL Server Books Online for more information. ...
    (microsoft.public.dotnet.framework.adonet)
  • problem with updateing foreign key to null value
    ... "deassign" a child from it's parent by updating it's foreign key field to ... update statements are executed and no errors are raised. ... Updated the child with a different parent key does however work!!! ... So it would seem SQLXML doesn't detect a change when updating to null?! ...
    (microsoft.public.sqlserver.xml)
  • Re: Records not inserting...
    ... > I am totally new to SQL server. ... However, in a trigger, would I be ... test the effects of certain data modifications and to set conditions for ... UPDATE statements. ...
    (microsoft.public.sqlserver.odbc)
  • Re: Statement handle still Active: prepare_cached
    ... The tests use SQLite for the database. ... get the warnings and I don't want new statement handles added to the ... You're right that INSERT or UPDATE statements shouldn't have Active set. ... but *every* CPAN tester sees this result. ...
    (perl.dbi.users)
  • Re: DB modification tracking
    ... tables that are accessible inside the trigger body. ... stores copies of the affected rows during DELETE and UPDATE statements. ...
    (microsoft.public.sqlserver.programming)