G'day AhmedABdo,

As a starting point, take a look at DBI: Placeholders and Bind Values.

The information in your post is rather vague and has forced me to make guesses. For instance, my first guess about Input was that it's supposed to be what's in your database; however, I see no SQL select statements to read this data. It could be from a file but there's no code performing I/O to read it. In fact, I see nothing where you get this Input.

Here's some suggestions:

• The where clause of your update statement looks like it needs more work such that you're only updating one row at a time. Maybe the addition of "and ID.Name = ?".
• The while condition looks wrong. That probably needs to be based on an SQL select statement.
• The "$run->execute" should be inside the while loop and will need arguments (to match whatever placeholders you decide to use).

Finally, telling us "it does not work" is not at all helpful. See "How do I post a question effectively?" for more details.

The first time you execute an SQL statement like this:

update table set ID = 10 where ID = 5"
[download]

I would suggest using a SELECT statement to get all the rows having ID=5, and then for each row returned by that query, do an update with desired new value for ID:

my $update_sth = $dbh->prepare( "update table set ID=? where ID=5 and 
+Name=?");
my $select_sth = $dbh->prepare( "select Name from table where ID=5" );

my $new_ID = 10;
$select->execute;

while ( my $row = $select->fetchrow_arrayref ) {
    my $name = $$row[0];
    $update_sth->execute( $new_ID++, $name );
}
[download]

Updated to fix a typo in the 2nd line of the code snippet (was "IS=5") -- thanks kcott!

graff: kcott raised the most important points. To expand on that a bit...

Kind sorta almost ... the question/problem feels more at the fizz-buzz stage, the OP has a good idea (almost) its just translating it into code, and working through the hurdles, thats the issue

For example, if he can get $max to affect add_max ... then notice the issue with the update you mentioned

then its just a matter of adding a update limit clause

My query is if the table.ID equal to 5, set the first one to 10, then the second one 11 and the third one 12 and so on

Actually, a database accessed by SQL has no concept of "first one" or "next one". Whichever record is returned "first" (or "next") is not strictly defined. It may be the same every time or may be different, subject to unknown variables, hidden rules or the phases of the moon.

If you want to impose "order" into this chaos, you must ask the database engine to sort the records for you ("ORDER BY clause").

a database accessed by SQL has no concept of "first one" or "next one". Whichever record is returned "first" (or "next") is not strictly defined.

While the second statement is accurate, the first is not. At least, not without context.

Technically, there most definitely is an order, as the data is stored in files, and those records do not usually move. And when they do move, there is an identifier in place saying so, until a specific operation removes it.

So, though not strictly defined, there is definitely a first record, and that is why it is always returned first in a vanilla statement. However, it cannot be relied upon because it can change from an outside statement affecting storage, or even a recent caching that included an order by (or a group by, which does its job via a binary sort.)

If you'd like to be technically correct, you would drop the words "a database accessed by" and use the KISS* method, "there is nor first or last in SQL, without an explicit ORDER BY clause."

--

*Keep It SQL, Stupid

That is exactly why I put "first one" and "next one" in "quotes".

And I said the DB does not have the concept of first or next (meaning the next one in an ordened sequence). It is simply not existing in an SQL database whose working is based on set-theory. You have to impose order yourself. So the OP by not defining the ordering was relying on a mechanism that does not exist when he referenced an operation on a sequence of records.

CountZero

A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

My blog: Imperial Deltronics

Crossposted at http://stackoverflow.com/questions/33110626/increase-a-value-inside-mysql-query-using-perl. I cross-linked this cross-postings for maximum collaboration efficiency

Speaking of collaboration, http://stackoverflow.com/questions/1167885/update-sql-with-consecutive-numbering says you can do it using mysql5, like

SET @a:=10;
UPDATE 
    table
SET
    table.ID=@a:=@a+1
WHERE
    table.ID=5
[download]

In sqlite I employ limit

#!/usr/bin/perl --
use strict;
use warnings;
use DBI;
use DBD::SQLite;
use Data::Dump qw/ dd /;

unlink 'goner.sqlite';

my $dbh =  DBI->connect(
    "DBI:SQLite:database=goner.sqlite",
    undef, undef,
    {qw/RaiseError 1/}
);
eval {
    $dbh->do('CREATE TABLE NameId ( ID INTEGER , Name TEXT );');
    $dbh->do(q!INSERT INTO NameID ( ID, Name ) VALUES ('1','bar');!);
    $dbh->do(q!INSERT INTO NameID ( ID, Name ) VALUES ('2','bar');!);
    $dbh->do(q!INSERT INTO NameID ( ID, Name ) VALUES ('5','FEE');!);
    $dbh->do(q!INSERT INTO NameID ( ID, Name ) VALUES ('5','FII');!);
    $dbh->do(q!INSERT INTO NameID ( ID, Name ) VALUES ('5','FOO');!);
} or warn "$@";


dd( $dbh->selectall_arrayref('select * from  NameID' ) );
dd( $dbh->selectrow_array(q{ select COUNT(*) from  NameID WHERE ID='5'
+ } ) );


#~ http://www.sqlite.org/compile.html#enable_update_delete_limit
#~ https://www.sqlite.org/lang_update.html
#~ https://dev.mysql.com/doc/refman/5.0/en/update.html
my $sql = qq{
UPDATE
    NameID
SET
    ID=?
WHERE
    ID='5'
LIMIT
    1
};

my( $count ) = $dbh->selectrow_array(q{ select COUNT(*) from  NameID W
+HERE ID='5' } );
my $nid = 10;
while( $count ){
    dd({ rowsaffected => $dbh->do( $sql, {}, $nid ) });
    dd( $dbh->selectall_arrayref('select * from  NameID' ) );
    $nid++;
    $count--;
}
undef $dbh;
unlink 'goner.sqlite';
__END__
[[1, "bar"], [2, "bar"], [5, "FEE"], [5, "FII"], [5, "FOO"]]
3
{ rowsaffected => 1 }
[[1, "bar"], [2, "bar"], [10, "FEE"], [5, "FII"], [5, "FOO"]]
{ rowsaffected => 1 }
[[1, "bar"], [2, "bar"], [10, "FEE"], [11, "FII"], [5, "FOO"]]
{ rowsaffected => 1 }
[[1, "bar"], [2, "bar"], [10, "FEE"], [11, "FII"], [12, "FOO"]]
[download]

I did this code, but it does not work.

Why do you think that is?

have you checked to see if the value of  $add_max changes after each iteration of the loop?

yes, but I want to change the value of ID which does not change

yes, but I want to change the value of ID which does not change

So what do you think you have to do to get $add_max to change?

What I would do is this ... and no, I’m not going to write or describe the actual code for you, but rather just the idea:

(1)   Use placeholders for each query, such as SELECT primary_key FROM table WHERE column = ?.   Notice the question-mark, which is not enclosed in quotes.   That’s a placeholder.   You can now prepare your statement-handle and execute it many times, providing each time an array of values which will be substituted (left to right) for each placeholder in the prepared query.   This is more efficient and more secure.

Important Note:   If ID is the primary key ... well, first of all, changing the key is usually not a good idea (since the need to do so implies that the key’s value carries meaning, which is not a good thing to do with a database key) ... second, if you are incrementing the keys you must add ORDER BY ID DESC to your SELECT statement because you must increment the highest key-value first.   Your SELECT statement will not encounter the record a second time (once the record-set has been pulled, it remains unchanged), but you must avoid non-unique values in a primary key field.   If you do not specify the order in this (special ...) case, key-collisions will occur and the update will fail for that reason.   If you do need an ID field for some other reason, then define another (say, auto-increment ...) primary key known only to the database.

(2)   Prepare two queries:   a SELECT statement which finds all of the rows that you want to update, retrieving their primary key and the value you want to change, and a statement like UPDATE table SET column = ? WHERE primary_key = ?.   (Two placeholders.)

(3)   If possible, use SQL transactions.   You will begin the transaction before starting the loop, and “commit” after the loop ends.   In this way, either all of the updates happen, or (if you “rollback”) none of them do.   This is very important for maintaining consistency especially among other updates being made by others.   It is also more efficient.   Note that MySQL can do this with InnoDB but not MyISAM tables (iirc).

(4)   Now, to actually do the work, begin a transaction.   Now, execute the SELECT query and loop through the rows returned.   For each, execute the already-prepared UPDATE statement, supplying the primary-key and new value.   After the loop ends, commit.   Wrap the whole thing (after begin-transaction) in an eval block such that, if an exception is thrown, you can roll back the transaction, leaving your database just like it was before the program started.   (All of the UPDATEs happen, or none of them do.)

This is much stronger than simply updating values based on their original value, which can be very problematic because one update affects the next one.   Here, you are selecting (by key) the rows that you wish to update, then you are updating them (identifying them by key).   You are efficiently using placeholders to let you build and prepare the two queries once and then to execute (the UPDATEs) many times.   You are using transactions both for efficiency and to ensure that no other database user will see an incomplete set of changes.