Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change

Comment on

( #3333=superdoc: print w/replies, xml ) Need Help??

Whether the fact that you can't reset the columns to NULL means you can't use this solution or not depends on the situation. What if all the columns in question disallow NULLs? I would not consider this a general reason to frown upon this solution. It's just something you have to keep in mind. How often do you actually want to set something to NULL?

I do not understand the second reason. Sounds like you thought that building dynamic SQL including the values would be the best solution then. And I'm actually not sure what log file do you mean. Could you explain?

Without benchmarking I would not dare to say whether the additional burden of "updating" some fields by the old values is bigger that the burden of either recompiling the SQL each time or keeping an exponential number of prepared statements. And I bet the results would differ a lot depending on the indexes and triggers on the table and the size of those columns.

Sure it's not dificult (in Perl) to create a query containing just the columns you want to update. The problem is that this way you either force the SQL server to parse and compile the statement each time or you have to cache a potentialy big number of different statements, one for each combination of updated columns. Both are straining the DBMS and the second one heavily complicates the code.

And there is actually one more reason why this IS quite often the one good solution is that quite often you want to keep all SQL inside stored procedures. That way you may rename and/or split tables without having to search for SQL in all programs and pages that might access the database. Plus the DBAs can see/modify/optimize all SQL that's gonna get executed against the database. Neither nor VIPeR actually allow any inline SQL, all database access has to go via stored procedures.

And neither constructing and executing some ad-hoc SQL inside the stored procedures nor having an explosion of IFs seem like a very good solution to me. (The first would not be accepted by the Monster DBAs anyway.)

Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live.
   -- Rick Osborne

Edit by castaway: Closed small tag in signature

In reply to Re: Re: Re: How to get a placeholder to keep the current column value? by Jenda
in thread How to get a placeholder to keep the current column value? by liz

Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":

  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?

    What's my password?
    Create A New User
    and all is quiet...

    How do I use this? | Other CB clients
    Other Users?
    Others browsing the Monastery: (4)
    As of 2018-01-20 03:00 GMT
    Find Nodes?
      Voting Booth?
      How did you see in the new year?

      Results (226 votes). Check out past polls.