3

I need to set a value to 1 in table where the count for a given role in another table is > 1.

The table cursillo.members has fields memid and hsd. Table cursillo.teams has fields memid (the link between the two) a field called movement and another called role.

What I am trying to accomplish is something similar to this:

update cursillo.members_eligible p, cursillo.teams pp
   set p.hsd=1     
 where p.memid = pp.memid AND (pp.role = 'HSD' OR pp.role = 'SD')
   and pp.movement = 'Cursillo' AND count(pp.role) > 1;

or this:

update members_eligibile
   set hsd=1 
  from teams 
 where teams.memid=members_eligible.memid 
   and (teams.role = 'HSD' OR teams.role = 'SD')  
   and teams.movement = 'Cursillo' 
   and count(teams.role) > 1;

In other words if a given memid has more than one record in the cursillo.teams table where the value of the role is equal to either HSD or SD, then set cursillo.members_eligible.hsd to 1.

I can't figure out to handle the count() part.

Thanks, Mike Reed

Marvo
  • 17,845
  • 8
  • 50
  • 74
Mike Reed
  • 33
  • 2

2 Answers2

1

Possible duplicate question:

MySQL - Using COUNT(*) in the WHERE clause

Try using the 'having' keyword

Here's the relevant section from the linked answer (in this case, a select statement):

select gid
from `gd`
group by gid 
having count(*) > 10
order by lastupdated desc

It looks like 'having' can only be used in select statments though: http://www.mysqltutorial.org/mysql-having.aspx

So you may need to have your update's where clause include a select statement:

update members_eligibile
   set hsd=1 
  from teams 
 where teams.memid=members_eligible.memid 
   and (teams.role = 'HSD' OR teams.role = 'SD')  
   and teams.movement = 'Cursillo' 
   and members_eligible.memid IN
   (SELECT members_eligible.memid from members_eligible where teams.memid=members_eligible.memid  having count(teams.role) > 1);

You will have to adjust the select statement, I haven't tested it

Community
  • 1
  • 1
ContextSwitch
  • 2,830
  • 6
  • 35
  • 51
  • I am new at this MySql stuff and learning how to use the commands. This suggestion, which I found when I did a search, is good, but I don't know how to incorporate it into what I need to Accomplish. Does anyone have any thoughts? – Mike Reed Nov 13 '12 at 22:16
  • It looks like having can only be used with select: http://www.mysqltutorial.org/mysql-having.aspx Try an update with a select subquery, ill update my answer – ContextSwitch Nov 14 '12 at 17:31
0

SQL Server 2005+

UPDATE x
SET x.hsd = 1
FROM (SELECT e.hsd, COUNT(*) OVER (PARTITION BY t.role) AS cnt
      FROM teams t JOIN members_eligibile e ON t.memid = e.memid
      WHERE (t.role = 'HSD' OR t.role = 'SD') AND t.movement = 'Cursillo') x
WHERE x.cnt > 1
Aleksandr Fedorenko
  • 16,594
  • 6
  • 37
  • 44
  • Thanks Alexander. I copied this into editor for MySQL and I can't get it to run. Might be some syntax difference between SQL Server 2005+ and MySql. It did give me a place to study and learn though so I'll keep playing with it to see if I can figure it out. – Mike Reed Nov 13 '12 at 22:26