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

[Jenda]

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.

Edit by castaway: Closed small tag in signature

[Anonymous Monk]

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?

[Jenda]

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

Edit by castaway: Closed small tag in signature

[giulienk]

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.

[liz]

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