dslreports logo
 
    All Forums Hot Topics Gallery
spc
Search similar:


uniqs
1936

Nordy
join:2007-10-20

Nordy

Member

Looping through all the records inside a MySql event

Can you please help me with looping inside a MySql event?

I did this event in MySql, but I guess I'm missing something. It works if I supply a limit = 1, which means it isn't looping thru the thing. How can I do that?

This works. (For only one record)
    BEGIN
    
    DECLARE c VARCHAR(2);
    DECLARE vElm INT(10);
    DECLARE vType TINYINT(1);
    
    select distinct(country) into c from votes limit 1;
    
    select votedElm, voteType into vElm, vType from votes where country = c limit 1;
    
    IF vType = 0 THEN
    update likes set votes = votes+1 where id = vElm and country = c and type = 10;
    ELSE IF vType = 1
    update likes set votes = votes-1 where id = vElm and country = c and type = 10;
    END IF;  
    
    END
 

This will not. Note: The limit 1 has been removed
    BEGIN
    
    DECLARE c VARCHAR(2);
    DECLARE vElm INT(10);
    DECLARE vType TINYINT(1);
    
    select distinct(country) into c from votes;
    //Need to loop here. This returns multiple records.
    
    select votedElm, voteType into vElm, vType from votes where country = c; 
    //Need to loop here too. This too returns multiple records.
    
    IF vType = 0 THEN
    update likes set votes = votes+1 where id = vElm and country = c and type = 10;
    ELSE IF vType = 1
    update likes set votes = votes-1 where id = vElm and country = c and type = 10;
    END IF;  
    
    END
 

How is looping done inside a MySql event?
Thanks

hairspring
join:2007-11-23
Oakville, ON

hairspring

Member

If you've already exhausted set-based approaches, you could use a cursor:

»dev.mysql.com/doc/refman ··· ors.html

cursors are a traditional way of iterating over variables.

Nordy
join:2007-10-20

Nordy

Member

I'm breaking my head on that right now. I'll post a reply with the answers soon.

Toad
join:2001-07-22
Bradford, ON

Toad to Nordy

Member

to Nordy
Think you want a join for the update rather than looping through them..

Something like
update likes, votes set votes = votes + case when voteType = 0 then 1 when voteType = 1 then -1 else 0 end where likes.id = votes.votedElm and votes.country = likes.country and votes.country = 10 and votes.type = 10
 

Nordy
join:2007-10-20

Nordy

Member

I'll give this a try too.

hairspring
join:2007-11-23
Oakville, ON

1 edit

hairspring to Toad

Member

to Toad
said by Toad:

Think you want a join for the update rather than looping through them..

Something like

update likes, votes set votes = votes + case when voteType = 0 then 1 when voteType = 1 then -1 else 0 end where likes.id = votes.votedElm and votes.country = likes.country and votes.country = 10 and votes.type = 10
 

I don't think this will aggregate votes. A sum() and group by statement is needed to deliver the total vote change to the updated row, otherwise MySQL will randomly pick one of the matching rows and apply that (discarding the rest).

Toad
join:2001-07-22
Bradford, ON

Toad

Member

Might be right. I tested on a small sample set.
Toad

Toad to Nordy

Member

to Nordy
 
update likes l join (select count(*) cnt, country, votedElm, voteType from votes group by country, votedElm, voteType) v on l.id = v.votedElm and v.country = l.country set l.votes = l.votes + case when v.voteType = 0 then 1 when v.voteType = 1 then -1 else 0 end * v.cnt where l.country = c and l.type = 10
 
 

See if this 'off the cuff' response is closer.

hairspring
join:2007-11-23
Oakville, ON

hairspring

Member

said by Toad:

[code]

update likes l join (select count(*) cnt, country, votedElm, voteType from votes group by country, votedElm, voteType) v on l.id = v.votedElm and v.country = l.country set l.votes = l.votes + case when v.voteType = 0 then 1 when v.voteType = 1 then -1 else 0 end * v.cnt where l.country = c and l.type = 10

[/code]

See if this 'off the cuff' response is closer.

I think this one will either update with votes in one direction and not the other.

Slightly modified.

[code=sql]
UPDATE likes l
JOIN (SELECT SUM(CASE WHEN voteType = 0 THEN 1 WHEN voteType = 1 THEN -1 ELSE 0 END) AS 'sum_votes',
votedElm,
country
FROM vote
GROUP BY votedElm, country) v
ON l.country = v.country,
l.id = v.votedElm
SET l.votes = l.votes + v.sum_votes;
[/code]

cowboyro
Premium Member
join:2000-10-11
CT

cowboyro to Nordy

Premium Member

to Nordy
The original code is wrong
DECLARE c VARCHAR(2);
select distinct(country) into c from votes limit 1;
 
The one above works because you can at most select 1 record into a scalar...

Nordy
join:2007-10-20

Nordy

Member

In the end i used a cursor inside the event and did something similar to the code above. Gets the job done for now