Update row

update row

DBIx::Custom Documents

update a row by update method.

$dbi->update(
  {title => 'Perl', author => 'Ken'},
  table => 'book',
  where => {id => 1},
);

First argument is updated data which is hash reference. table option is set to table name. where option is set to hash reference or DBIx::Custom::Where object. The following SQL is executed.

update book set title = ?, author = ? where id = ?;

Corresponding values is embdded into place holder.

update method's options

update method can use all of execute method options, and use the following ones.

id - ID

Set parameter value without column name. primary_key must be set to column names.

id => 4
id => [4, 5]

Updated data using primary_key's column names is created.

$dbi->update(
  {title => 'Perl', author => 'Ken'}
  primary_key => ['id1', 'id2'],
  id => [4, 5],
  table => 'book'
);

This is same as the following one.

$dbi->update(
  {title => 'Perl', author => 'Ken'}
  where => {id1 => 4, id2 => 5},
  table => 'book'
);

prefix - prefix

Add some strings between update and TABLE_NAME.

prefix => 'or replace'

Example:

$dbi->update(
  {title => 'Perl'},
  table => 'book',
  prefix => 'or replace'
);

The following SQL is executed.

update or replace book set title = ?;

table - Table name

Table name.

table => 'book'

mtime - updated time

Set the inserted time to the specified column which mean updated time.

mtime => 'updated_time'

Default time format is YYYY-mm-dd HH:MM:SS. This can be changed by now attribute.

Example:

$dbi->update({title => 'Perl}, table => 'book', mtime => 'updated_time');

This is same as the following example.

use Time::Piece;
my $now_tp = localtime;
my $now = $t->strftime('%Y-%m-%d %H:%M:%S');
$dbi->update({title => 'Perl', updated_time => $now}, table => 'book');

where - where clause

Write where clause by where option.

# (1) Hash reference
where => {author => 'Ken', 'title' => 'Perl'}

# (2) String
where => "author = 'Ken' and title = 'Perl'";

# (3) Array reference [ Array reference, Hash reference ]
where => [
  ['and', 'author = :author', 'title like :title'],
  {author => 'Ken', title => '%Perl%'}
]

See where option detail and How to create where clause to know where option usage.

wrap - wrapping place holder with some string

Wrapping place holder with some string. If you want to edit place holder, use this option. Palce holder is replaced by the specified code reference.

wrap => {price => sub { "$_[0] + 5" }}

Example:

$dbi->update(
  {price => 100},
  table => 'book',
  where => {id => 1}
  wrap => {price => sub { "$_[0] + 5" }}
);

The following SQL is executed.

update book set price = ? + 5 where id = 1

Update row using constant value

If you update row using constant value, not place holder's value, use scalar reference.

$dbi->update(
  {title => 'Per', ctime => \"now()"},
  table => 'book'
  where => {id => 1}
);

The following SQL is executed.

update book set title = ?, ctime = now() where id = ?;