The way I address this sort of problem is to not address it, really, and it involves core elements of application design; first and foremost, I use an OO approach. What i do is set up an object that serves as a repository for the objects in the database. The repository handles all the SQL, and, if you want to minimize DB accesses, make the repository persistent and have it cache the objects (this works best, of course, if your primary means of fetching the objects is via their primary keys). To update an object, you check a copy out of the repository, modify it as needed (of course, you might need to make sure that it's hard to change certain values, e.g. primary keys) , then call $repository->update($object);, where the update call basically looks like what you have. , where foo, bar, baz designate all the updateable fields. If you've pre-cached the object, there's no need to call a new "fetch" routine to figure out what the old values are. Of course, that might not be appropriate.

I've played with trying to build the SQL dynamically (by checking to see which fields on the object are different from the one stored in the repository, and generating an SQL UPDATE statement on the fly), but that can be error-prone and time-consuming.

Another option eschews database independence -- and yes, I'm well aware that this option is probably of no solace to you, unless Postgres is an option. If you have a DBMS that supports triggers, you can just issue all the SQL as-is, and write your triggers to refuse to overwrite null fields; in PL/SQL (Oracle), such a trigger would look something like this:

  -- :NEW.XXXX refers to the value of the XXXX field specified in the 
+update statement
   -- :OLD.XXXX refers to the value in the record being updated
  IF :NEW.foo IS NULL THEN
    :NEW.foo := :OLD.foo
  END IF;
[download]

This does have the drawback, as I mentioned, of being non-portable (but I have sinned, I do use it on occasion). If your application's ecological niche makes the precaching repository approach feasible, I'd suggest that, otherwise I believe that the dynamic SQL is the best approach. Good luck!

---

---

• Are you posting in the right place? Check out Where do I post X? to know for sure.
• Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:

  <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>

• Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
• Want more info? How to link or How to display code and escape characters are good places to start.