Justafriend Posted January 21, 2016 Share Posted January 21, 2016 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted January 21, 2016 Share Posted January 21, 2016 Put "yes" inside single quotes so SQL knows it is a string literal and not a column name ... set morethan5 = 'yes' ... Quote Link to comment Share on other sites More sharing options...
Justafriend Posted January 21, 2016 Author Share Posted January 21, 2016 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted January 21, 2016 Solution Share Posted January 21, 2016 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. Quote Link to comment Share on other sites More sharing options...
Justafriend Posted January 21, 2016 Author Share Posted January 21, 2016 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.