updating table query

[Justafriend]

Ok i have a table with headings

username   useremail   randomnumber   neptune   morethen5

 

 

What im trying to do is if a username has more than 5 records where neptune isnt null update  the morethan5 column for all username with yes

 

this is the query i have

update ballot
set morethan5 = yes
where username in
(
  select * from
  (
    select neptune
    from  ballot
    group by username
    having count(1) > 5
  ) x
)

i got first of all an error saying yes  column didnt exist so i created a new column called yes  and it isnt updating any records

[Barand]

Put "yes" inside single quotes so SQL knows it is a string literal and not a column name
 

...
set morethan5 = 'yes'
...

[Justafriend]

that fixed the error  but its still not updating the column  here is an example of  the table

 

 

 

 pie me@me.com 46276 \N  
taz me@me.com 49042 \N  
taz me@me.com 49042 \N  
wicked me@me.com 41494 \N  
wicked me@me.com 41494 \N  
nnn me@me.com 38915 neptune  
nnn me@me.com 38808 neptune  
nnn me@me.com 38915 neptune  
nnn me@me.com 38808 neptune  
nnn me@me.com 38915 neptune  
nnn me@me.com 38808 neptune  

what im trying to do is count how many  for each username that has  a non null  in the column neptune and if ithe same player has more then 5 Non Null values to mark the morethan5 column with yes

Edited January 21, 2016 by Justafriend

[Barand]

try

UPDATE ballot
INNER JOIN 
    (
    SELECT username
    , COUNT(neptune) as ncount
    FROM ballot
    GROUP BY username
    HAVING ncount > 5
    ) n USING (username)
SET morethan5 = 'yes'

However, it is bad practice to store derived data like that in a db table.

[Justafriend]

thank you for all you do and your help this table is only for a week long event we use each year and when its done it  is backed up  for next year