CODEWITHSUNDEEP
mysql
Islam Muntasser
Islam Muntasser

Reputation: 188

MySQL- Select and Update at the same time

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 ?

Upvotes: 3

Views: 13207

Answers (4)

Islam Muntasser
Islam Muntasser

Reputation: 188

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

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.

Upvotes: 3

Vivek Singh
Vivek Singh

Reputation: 2447

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

Upvotes: -3

Roy Shmuli
Roy Shmuli

Reputation: 5019

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

update outbox set Status = 1 where Status = 0

Upvotes: 0

Related Questions