While I'm (finally) getting to grips with using DBI placeholders, I realise that there may be one situation where placeholders are not the solution.

The situation: I'm writing an XML to RDBMS Perl layer that should allow my customer to use XML to update records in a database. For each type of information (e.g. address information), there are a number of expected nodes in the XML that correspond to columns in a table in the database. For example, a complete description of a record would be:

<address id="34567">
 <phone>123</phone>
 <street>Fred Ziffel Avenue 42</street>
 <zip>123456789</zip>
 <city>Hooterville</city>
</address>
[download]

Now, when the customer supplies the following XML:

<address id="34567" mode="update">
 <phone>456</phone>
 <!-- note the absence of other nodes -->
</address>
[download]

I want it to update just the "phone" column for that record in the database.

Now, at least in MySQL you can put the following in the SQL to indicate you don't want to change the value of a column. For example: the "street", "zip" and "city" fields.:

UPDATE address SET
 phone="456",
 street=street,
 zip=zip,
 city=city
WHERE ID="34567"
[download]

so in principle this should allow you to do a prepare once because the query would always be the same. Only those fields that you don't want changed, get their field name replaced rather than an actual value.

However, it seems to me that it is not possible to do this using placeholders, as you can only specify values with placeholders, rather than a name of the column.

Of course, I could always fetch the old values from the record in the database first, but I feel that sort of defeats the purpose. Or would it?

Anyway, my question: can I use placeholders in case I don't want to change the value of a column, without obtaining the old value from the record first?

Liz

Update:
Thanks to runrig and pg: it would seem that "Dynamic SQL" (never knew there was a phrase for it, as it is the way I was always used to doing it using the older Mysql.pm modules) is the way to go for me, as the number of different field combinations would provide me with a combinatorial explosion. I was hoping that I could use prepare() for this, but it seems that that just isn't possible.

Good News:
Jenda++ shows in Re: How to get a placeholder to keep the current column value? that there _is_ a way to do this with a pre-prepared statement handle, even in MySQL!