0

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.

iCantSeeSharp
  • 3,880
  • 4
  • 42
  • 65
to4dy
  • 128
  • 1
  • 1
  • 10
  • You mean that you want _all updated rows to be returned_ and not have to select first to get the rows and then update? – Joachim Isaksson Aug 09 '13 at 10:25
  • 3
    So you mean you want to update `booled = 1` where `booled = 0`? – Himanshu Aug 09 '13 at 10:25
  • Exactly, but as a return i need the tables where booled was 0 at the beginning. – to4dy Aug 09 '13 at 10:30
  • you many find this question useful http://stackoverflow.com/questions/2334712/update-from-select-using-sql-server – HariKrishnan Aug 09 '13 at 10:36
  • 1
    Sadly, MySQL does not have SQL Server's [OUTPUT](http://technet.microsoft.com/en-us/library/ms177564.aspx) clause, so probably not in a more elegant way than sending both commands inside a transaction (or using a stored procedure). – Joachim Isaksson Aug 09 '13 at 10:37

4 Answers4

4

Why not this ?

UPDATE mytable SET booled=1 WHERE booled=0
Raptor
  • 53,206
  • 45
  • 230
  • 366
  • This doesn't return the rows where booled was 0 to begin with. – Joachim Isaksson Aug 09 '13 at 10:34
  • I tried to avoid this, I need the output of the select where booled=0 and since the table could change during execution i wanted to do everything in one atomic query. If possible – to4dy Aug 09 '13 at 10:36
4

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;
mvp
  • 111,019
  • 13
  • 122
  • 148
0

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);
Harry
  • 2,429
  • 4
  • 21
  • 26
0

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) );

Rakesh
  • 77
  • 11