CODEWITHSUNDEEP
mysqlsql
to4dy
to4dy

Reputation: 128

Database update and select at the same time

is it possible to make a select and update at the same time?

select id,name from mytable where booled = 0

UPDATE mytable SET booled=1 WHERE (select id,name from mytable where booled = 0)

So to say those 2 commands in one.

Upvotes: 0

Views: 167

Answers (4)

mvp
mvp

Reputation: 116447

There is no need to reinvent the wheel - you simply need to properly use transactions. MySQL supports transactions as long as you use InnoDB engine for your tables (old MyISAM would not work).

Following series of statements would do what you want:

BEGIN;
SELECT id,name FROM mytable WHERE booled=0;
UPDATE mytable SET booled=1 WHERE booled=0;
COMMIT;

Depending on your programming language and database drivers you may not be able to directly use begin/commit transaction statements, but instead use some framework specific mechanisms to do that. For example, in Perl, you need to do something like this:

my $dbh = DBI->connect(...);
$dbh->begin_work(); # This is BEGIN TRANSACTION;
my $sth = $dbh->prepare(
    "SELECT id,name FROM mytable WHERE booled=0");
$sth->execute();
while (my $row = $sth->fetchrow_hashref()) {
     # do something with fetched $row...
}
$sth->finish();
$dbh->do("UPDATE mytable SET booled=1 WHERE booled=0");
$dbh->commit();    # This is implicit COMMIT TRANSACTION;

Upvotes: 4

Rakesh
Rakesh

Reputation: 77

You can make use of 'EXISTS' ..its much faster than "In"

select id,name from mytable where booled = 0;
UPDATE mytable t1
SET booled=1 WHERE exists (select 1 from mytable t2
where booled = 0 and
t1.column=t2.column(join condition) );

Upvotes: 0

Raptor
Raptor

Reputation: 54268

Why not this ?

UPDATE mytable SET booled=1 WHERE booled=0

Upvotes: 4

Harry
Harry

Reputation: 2546

Try delimiting the two statements with a semicolon:

select id,name from mytable where booled = 0;
UPDATE mytable SET booled=1 WHERE (select id,name from mytable where booled = 0);

Upvotes: 0

Related Questions