1

I have this query 

SELECT * FROM outbox where Status=0 ;

then I need to update the selected records so Status should be equal 1

i.e (UPDATE outbox(selected records from SELECT query) SET Status =1 )

any help ?

Islam Muntasser
  • 160
  • 3
  • 4
  • 16

4 Answers4

3

This is a much harder problem than it sounds. Yes, in the simplistic case where you are only thinking of one user and a few records, it seems easy. But, databases are designed to be ACID-compliant, with multiple users and multiple concurrent transactions that can all be affecting the data at the same time. And there is no single statement in MySQL that does what you want (other databases support an OUTPUT clause, RETURNING or something similar).

One structure that will work in MySQL is to place the items in a temporary table, then do the update, then return them. The following shows the semantics using transactions:

start transaction;

create temporary table TempOutboxStatus0 as
    select *
    from outbox
    where status = 0;

update outbox o
    set status = 1
    where status = 0;

select *
from TempOutboxStatus0;

commit;

For the update, I actually prefer:

    where exists (select 1 from TempOutboxStatus0 t where t.outboxid = o.outboxid);

because its intention is clearer -- and the code is safer in case the conditions subtly change.

Note: you may want to use explicit table locks. Such considerations depend on the storage engine you are using.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • What's to prevent `outbox` being updated between the `create temporary table` and `update` commands? Surely one should use a locking read, e.g. `SELECT * FROM outbox WHERE status=0 FOR UDPATE` (and so doing avoids the need for a temporary table)? – eggyal Mar 21 '15 at 13:15
  • @eggyal . . . Nothing. However, the transaction *should* ensure ACID-compliance -- although this might not always be true in MySQL. My concern isn't updates during the `select`. It is updates between the `select` and the `update`. – Gordon Linoff Mar 21 '15 at 13:17
1
BEGIN
        Start transaction;
        SELECT * 
            FROM 
                outbox 
            where 
                Status = 0 and
                Is_Expired = 0 and
                Service_ID=p_service_id
        order by    
                Next_Try_Date asc FOR Update;          

        update outbox 
            set 
                Status=1 
            where
                Status = 0 and
                Is_Expired = 0 and
                Service_ID=p_service_id;
        commit;

END

is this possible .. it seems it works with me

Islam Muntasser
  • 160
  • 3
  • 4
  • 16
0

You can do something like that, the outbox is your table:

update outbox set Status = 1 where Status = 0
Roy Shmuli
  • 4,979
  • 1
  • 24
  • 38
  • I need the result set from select query to use it in my application then I need to update this result set !! FYI this code is part of stored procedure – Islam Muntasser Mar 21 '15 at 11:26
  • What is query? table? – Roy Shmuli Mar 21 '15 at 11:28
  • @IslamMuntasser but the result set from the first query is every row with `Status = 0`?! –  Mar 21 '15 at 11:30
  • the query Select * from outbox where Status= 0 ; (this will be the result set) then i need to update this result set so Status will be "1" so how can I update and select in the same query !! – Islam Muntasser Mar 21 '15 at 11:31
  • I know that I can select to temp table then update the main table by the IDs of the temp table .. but it headache .. – Islam Muntasser Mar 21 '15 at 11:35
  • 1
    @IslamMuntasser: You can't "update and select in the same query". But doing so is rarely necessary: that you're trying to do it probably indicates poor design decisions have been made along the way. @Strawberry asked above for more information on why you're attempting it, the answer to which would help us to suggest a better design. However, as things stand and since "this code is part of stored procedure", you could simply perform the `SELECT` first and then perform the `UPDATE` afterwards: the sproc will output the resultset from the `SELECT` and then update the table's contents. – eggyal Mar 21 '15 at 11:38
  • Ok .. the first part is good .. the sproc will return the resultset from the SELECT.. Then I need to Update the selected IDs from the select query where Status of them = 0 to be 1 – Islam Muntasser Mar 21 '15 at 11:41
  • @IslamMuntasser: So what's wrong with running the `update` command as given in Roy's answer above? The set of records that it will update should be the same as the set that were previously selected, unless you have concurrent connections that may have changed the data in the interim. If concurrency is your concern, then you will either need to use a [locking read](http://dev.mysql.com/doc/en/innodb-locking-reads.html) within a transaction (if your table uses a transactional storage engine such as InnoDB), or else explicitly [lock the table](http://dev.mysql.com/doc/en/lock-tables.html). – eggyal Mar 21 '15 at 11:44
  • No you don't. You just need the query @RoyShmuli has given you. – Strawberry Mar 21 '15 at 11:51
  • @Strawberry: How then would you handle a concurrent connection altering the data between the select and the update? – eggyal Mar 21 '15 at 11:56
-3

you can do it like below

$sql=mysql_query("SELECT * FROM outbox where `Status`=0");
while($result=mysql_fetch_array($sql))
{
$update="UPDATE `outbox` SET `Status` =1 where 
'your column name'='your previous fetched value');
}
Vivek Singh
  • 2,453
  • 1
  • 14
  • 27