You can do whatever you want by hand of course but this stuff gets hairy quickly and I would strongly recommend taking the learning curve hit up front for DBIx::Class or something similar. If you want a bit more more raw try SQL::Abstract.

use SQL::Abstract;

my $sql = SQL::Abstract->new;

my %data = ( name => 'test_name',
             phone => 'test_phone',
             city => 'test_city',
             country => 'test_country' );

my ( $statement, @bind ) = $sql->update("phone", \%data);

printf("SQL --> %s\n        %s\n\n",
       $statement, join(",", @bind) );

delete @data{qw( phone city )};

( $statement, @bind ) = $sql->update("phone", \%data);

printf("SQL --> %s\n        %s\n\n",
       $statement, join(",", @bind) );

__END__
SQL --> UPDATE phone SET city = ?, country = ?, name = ?, phone = ?
        test_city,test_country,test_name,test_phone

SQL --> UPDATE phone SET country = ?, name = ?
        test_country,test_name
[download]

Use named parameters:

#!/usr/bin/perl

use warnings;
use strict;

update_table(
    name    => 'test_name',
    phone   => 'test_phone',
    city    => 'test_city',
    country => 'test_country'
    );
update_table(
    name    => 'test_name',
    phone   => 'test_phone',
    country => 'test_country'
    );
update_table(name => 'test_name', city => 'test_city');


sub update_table {
    my (%params) = @_;

    die "name parameter is required by update_table ()\n"
        if !exists $params{name};

    my @fieldNames = grep {$_ ne 'name'} keys %params;
    my @fields = map {"$_ = *"} @fieldNames;
    my $sql = "UPDATE table SET ";
    my @values = (@params{@fieldNames}, $params{name});

    $sql .= join ', ', @fields;
    $sql .= " WHERE name = '*'";

    print "$sql [values are @values]\n";
    return 1;
}
[download]

Prints:

UPDATE table SET country = *, city = *, phone = * WHERE name = '*' [va
+lues are test_country test_city test_phone test_name]
UPDATE table SET country = *, phone = * WHERE name = '*' [values are t
+est_country test_phone test_name]
UPDATE table SET city = * WHERE name = '*' [values are test_city test_
+name]
[download]

Note too that this uses place holders which should always be used to avoid quoting issues (and injection attacks for untrusted data sources).

Look up "sql place holder". The query should really be written with placeholders similar to the following:

    my $sql = "UPDATE table SET phone = ?, city = ?"
[download]

But this does not solve the problems:
• passing null parameters...
• placement of the comma...

bichonfrise74:

Actually, placeholders *do* handle the null parameter. Constructing the update statement can do the rest...

my @fields = qw(name phone city country);
my $SQL = "update table set "
.         join(", ", map { "$_=?" } @fields)
.         "where ....";
my $ST=$DB->prepare($SQL);
while (...) {
    my @values = (.....);
    $ST->execute(@values);
}
[download]

...roboticus

When your only tool is a hammer, all problems look like your thumb.

my $sql = qq! UPDATE table SET !
            . ( defined($phone) ? qq! phone = '$phone', ! : '')
[download]

Remember, you should really be using placeholders to prevent SQL injection attacks.

Remember, you should really be using placeholders to prevent SQL injection attacks.

There can only be SQL injection attacks if the OP would be passing in data from untrusted sources to the subroutines.

There is, however, a much better to use placeholders. It'll take care of dealing with undef values.

I had the same approach that you suggested but there is a problem with this... For example, if the phone and country are null, then the SQL that will be constructed will be:

UPDATE table SET city = 'test_city', FROM ...
[download]

This will be incorrect because of the comma after the 'test_city.'

I am not worried about SQL injection attack since this is really something internal and will not be exposed to the outside world.