CODEWITHSUNDEEP
mysql
Abeer Raza
Abeer Raza

Reputation: 15

create stored procedure for multiple update queries

select id as ids from challenges where expire_date > today

I need loop her for multiply ids like (foreach in php) 

foreach (ids) 

    update challenges set status = 'expired' where id = ids

    update user_challenge set challenge_status = 'Expired' where challenge_status = 'Pending' and challenge_id = ids 
    update user_challenge set challenge_status = 'Failed' where challenge_status = 'Accepted' and challenge_id = ids 

end for each;

can anyone please create stored procedure or single query to perform this, Thanks

Upvotes: 0

Views: 1538

Answers (2)

peterm
peterm

Reputation: 92785

Your single update query in MySql might look like this

UPDATE user_challenge u INNER JOIN
       challenges c ON u.challenge_id = c.id
   SET c.status = 'Expired',
       u.challenge_status = CASE WHEN u.challenge_status = 'Pending' THEN 'Expired' 
                                 WHEN u.challenge_status = 'Accepted' THEN 'Failed' END
 WHERE c.expire_date < CURDATE()

Here is SQLFiddle example

I believe the condition for expiration to make sense should be expire_date < CURDATE() (meaning expiration date should be less than today's date) which is reflected in the query.

Upvotes: 1

Axarydax
Axarydax

Reputation: 16603

You can declare a cursor, use the cursor to loop through the result from your query

This syntax is for T-SQL, but it should be similar for MySQL:

declare @id int
declare @cursor cursor;
set @cursor = cursor for select id as ids from challenges where expire_date > today
open @cursor
fetch next from @cursor into @id
while(@@FETCH_STATUS = 0)
begin
   update challenges set status = 'expired' where id = @id

    update user_challenge set challenge_status = 'Expired' where challenge_status = 'Pending' and challenge_id = @id
    update user_challenge set challenge_status = 'Failed' where challenge_status = 'Accepted' and challenge_id = @id 

  fetch next from @cursor into @id   
end
close @cursor
deallocate @cursor

Upvotes: 0

Related Questions