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

Good day, I am looking for a quick and simple way to update a row in mysql with a hash with as few of lines as possible. I have found this code for inserting a row, but I have been unable to reformulate it in to an UPDATE command, probably due to my beginner perl skills. 
#to insert a line
my $stmt = 'INSERT INTO some_table (' . join(',', keys %data_to_insert
+) . ') VALUES (' . join(',', ('?') x keys %data_to_insert) . ')';
$dbh->do( $stmt, undef, values %data_to_insert);

[download]

Replies are listed 'Best First'.
Re: Update mysql row with a hash
by stefbv (Priest) on Feb 11, 2010 at 06:35 UTC

    Something like this, maybe?

    # Transform hash into two arrays
my @fields = keys %{$record_ref};
my @values = map { $record_ref->{$_} } @fields;

my $sql
    = "UPDATE $table SET "
    . join( '=?,', @fields )
    . "=? WHERE $clauza_where";

# print "sql=",$sql,"\n";

eval {
    $sth = $dbh->prepare($sql);
    $sth->execute(@values);
    $dbh->commit;      # commit the changes if we get this far
};

if ($@) {
    warn "Transaction aborted because $@";
    $dbh->rollback;     # undo the incomplete changes
}

    [download]

    Update: fixed small typo regarding unmatched right curly bracket

[reply]
[d/l]
Re: Update mysql row with a hash
by desemondo (Hermit) on Feb 11, 2010 at 01:05 UTC
    If your database has any kind of data type contraints which its 99% likely it does, then your query will fail if your perl statement happens to try to stick a string into a date field, or a number into a bool. This is because hashes do not have a standard internal order, the returned order of keys or values is random**, unless you sort them. Even then, you can only ever be sure you'll get them in the same order if no keys are added or deleted.

    What does $stmt actaully look like if you print it out? If you then copy that into your DB query window and execute it, what error(s) do you get? That'll likely reveal the problem.

    Update:
    Actually, upon re-reading your question, I think you may be having problems due to mis-understanding SQL.
    The usual syntax for an UPDATE cmd is: 
    UPDATE "table_name"
SET column_1 = [value1], column_2 = [value2]
WHERE {condition}

    [download]
    I don't see how you can simplify that down to an "Update table " . keys %hash . " VALUES " . values $hash kind of structure.

    Update:Clarified post thanks to afoken

    ** But will return the same order for the same hash if compared within the same Perl 'run' AND the hash has not been modified, thanks afoken for correcting me.
[reply]
[d/l]
[select]
      This is because hashes do not have a standard internal order, the returned order of keys or values is random, unless you sort them.

      Correct, but keys, values, and each use the same order for the same hash. From perlfunc:

      Entries are returned in an apparently random order. The actual random order is subject to change in future versions of perl, but it is guaranteed to be in the same order as either the keys or values function would produce on the same (unmodified) hash. Since Perl 5.8.2 the ordering can be different even between different runs of Perl for security reasons [...].

      Alexander

      --
      Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)
[reply]
[d/l]
[select]
Re: Update mysql row with a hash
by tstock (Curate) on Nov 04, 2017 at 03:59 UTC 
    my ($id) = delete $data_to_insert{id};

my $sql = "UPDATE some_table SET "
    . join( '=?,', keys %data_to_insert ) . '=? '
    . 'WHERE id=?';

$dbh->do( $stmt, undef, values %data_to_insert, $id );

    [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom