Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

Re: How to get a placeholder to keep the current column value?

by Jenda (Abbot)
on Oct 26, 2003 at 21:31 UTC ( #302267=note: print w/ replies, xml ) Need Help??


in reply to How to get a placeholder to keep the current column value?

What about

UPDATE address SET phone = COALESCE(?, phone), street = COALESCE(?, street), zip = COALESCE(?, zip), city = COALESCE(?, city) WHERE ID=?
This way the SQL is static, can be prepared and if you pass an undef for some field it'll keep the old value. Of course this would not work if you did want to be able to set some fields to NULL, but otherwise this seems to be a nice solution.

P.S.: I have never worked with MySQL so I'm not sure there is a COALESCE() function there. It's supposed to return the first non-NULL parameter.

Jenda
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


Comment on Re: How to get a placeholder to keep the current column value?
Download Code
Re: Re: How to get a placeholder to keep the current column value?
by liz (Monsignor) on Oct 26, 2003 at 21:55 UTC
    Jenda++!

    This is what I was looking for! And according to the MySQL documentation it is supported by MySQL!

    It would seem to me that gmax needs to add another idiom to DBI Recipes! ;-)

    Liz

Re: Re: How to get a placeholder to keep the current column value?
by Anonymous Monk on Oct 26, 2003 at 22:54 UTC

    Clever. It will work, and it will do what the OP wanted. But it is not a clean solution.

    this would not work if you did want to be able to set some fields to NULL

    Right. And this is a good reason not to use this feature.

    A second good reason to avoid this idiom is that it makes debugging more difficult. If all updates look the same, it will be harder to spot the offending one in a log file whan you're hunting for bugs.

    A further good reason not to go for this solution is that you are straining the DBMS to encourage the programmer's laziness, and I am not talking of the Laziness that makes good coders.

    It isn't difficult to create a query using only the fields that are needed. Why looking for trouble?

      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 Monster.com 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.)

      Jenda
      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

Re: Re: How to get a placeholder to keep the current column value?
by giulienk (Curate) on Oct 27, 2003 at 08:07 UTC
    It seems that MySQL's COALESCE allows to input NULL values as if all the values are NULL, NULL will be returned.

    I quote from MySQL Manual:

    COALESCE(list)
        Returns first non-NULL element in list:
    
    mysql> SELECT COALESCE(NULL,1);
            -> 1
    mysql> SELECT COALESCE(NULL,NULL,NULL);
            -> NULL
    

    UPDATE: ops, i wasn't careful enough to check the solution offered. In that case NULL won't be returned so Jenda's assumption is right.


    $|=$_="1g2i1u1l2i4e2n0k",map{print"\7",chop;select$,,$,,$,,$_/7}m{..}g

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://302267]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (8)
As of 2014-09-03 09:01 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite cookbook is:










    Results (35 votes), past polls