josh097 has asked for the wisdom of the Perl Monks concerning the following question:

I have a ASP file with using perlscript. It has one form with 8 fields. After the user fills them in, they are inserted into a database. Afterwards they can either do nothing, update, or delete the entry. What I want to know is what is the best or most efficient way to check which fields where changed so that I can update the database.

I have all of the original entries in an array "@input" and the new entries (whether changed or not) in another array "@inputUpdate".

Now how can I compare these two in order to have the most 'accurate' sql query?

I was thinking of going through each field and comparing the one at a time like:

if ($input[0] ne $inputUpdate[0])
{
my $dbquery eq "UPDATE table_name SET column_name=$inputUpdate[0] WHER
+E column_name=$input[1]";
}

[download]
Or something like that (for some reason it didn't let me put the equals sign in the code instead of "eq"). But the problem is that I don't know which fields or how many of them will be changed.

Replies are listed 'Best First'.
Re: Best way to check which field(s) was changed
by Corion (Patriarch) on Dec 29, 2008 at 17:57 UTC

    Personally, I wouldn't bother with having a "most accurate" SQL, as most databases need to rewrite the whole row anyway.

    my $dbquery eq "UPDATE table_name SET column_name=$inputUpdate[0] WHERE column_name=$input[1]";

    doesn't do what you think it does, so it might help if you showed us the actual error message that Perl tells you.

    If you're bent on providing a "most accurate" SQL statement, most likely the following code can give you such a statement:

    use strict;
my @columns = qw(name title state);
my @input = qw(foo bar baz);
my @inputUpdate = qw(boing bar boo);

my $table_name = 'mytable';

my @new_value_clause = map { "$columns[$_] = ?" } grep { $input[$_] ne
+ $inputUpdate[$_] } 0..$#inputUpdate;
my @where_clause     = map { "$columns[$_] = ?" } 0..$#input;

my $sql = join "\n",
         "UPDATE $table_name",
         "SET " . join ",", @new_value_clause,
         "WHERE " . join " and ", @where_clause;

my $sth = $dbh->prepare($sql);
$dbh->execute(@inputUpdate, @input);

    [download]
[reply]
[d/l]
[select]

      Well

      my $dbquery eq "UPDATE table_name SET column_name=$inputUpdate[0] WHERE column_name=$input[1]";

      was just an example. So there is no error message to show you.

      I wanted to find all of the the fields that were changed and put them in the 'SET' part of the query and all of the ones that didn't change in the 'WHERE' part.

      That is what I meant by 'most accurate'. As opposed to putting just one field in the 'WHERE' section (unless all other fields where changed) just in case there where more than one row in that column with the same entry.

      I don't know if you understand what I mean. I do appreciate your help though.

[reply]
[d/l]
Re: Best way to check which field(s) was changed
by runrig (Abbot) on Dec 29, 2008 at 18:20 UTC
    Cue reference to Little Bobby Tables. Use placeholders!. Also don't worry too much about "what changed", unless one of the fields is a LOB. Prepare one update statement that updates the whole row. But use placeholders, or suffer the wrath of Little Bobby Tables.
[reply]
Re: Best way to check which field(s) was changed
by ccn (Vicar) on Dec 29, 2008 at 18:05 UTC
    This query: "UPDATE table_name SET column_name=$inputUpdate[0] WHERE column_name=$input[1]" can update more than one rows at once! Probably it is not what you want. There some unique index column must be used for update.

    I suggest to have two hashes: %input, and %inputUpdate instead of arrays. Those hashes contain ColumnName => value pairs.

    Than, assuming ID is identity column: 

    my %update =
      map {$_ => $inputUpdate{$_} } 
        grep {$inputUpdate{$_} ne $input{$_} }
          keys %input;

$dbh->do(<<"SQL", undef, values %update, $input{ID});
update  TableName
   set @{[ join ', ', map {"$_ = '?'"} keys %update]}
 where ID = ?
SQL
$dbh->commit;

    [download]
[reply]
[d/l]
[select]
Re: Best way to check which field(s) was changed
by JavaFan (Canon) on Dec 29, 2008 at 18:20 UTC
    Considering that most users won't update something when they haven't changed anything, and that it hardly matters for a database whether you changed 1 or several columns in a row, why not just always send an update query to the database, and let the database take care of the details? 
    UPDATE table_name
   SET column1 = ?, column2 = ?, column3 = ?, column4 = ?
       column5 = ?, column6 = ?, column7 = ?, column8 = ?
 WHERE key = ?

    [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom