SQL count returning one value (and not the total).

Question

Hi all.

I need to get the nid "alone" in this query.

SELECT count(nid) as total,nid FROM bird_countries b group by nid order by total asc`

Basically I need this to do something like

update table set field=1 where id in (the other query);

Right now, I'm getting the total of nid's and the nid, but can't find how to just get the nid values to use them.

An example of what is returning the first query is:

total nid
1     323
1     2645
1     526
2     123

So I just want to get the values 323, 2645, 526. In fact I don't want to get the 123 as the count for it is 2. I need to set one field on the table to 1 for the cases in which the count is 1.

UPDATE (second question): I get a good answer, but now I have an error applying it. My query is

update bird_countries set season="1" where nid in (SELECT nid FROM bird_countries group  by nid HAVING count(*) = 1)

and mysql says "You can't specify tablet target 'bird_countries' for update in FORM clause." Well, is there any easy/fast way to do this? I can duplicate the table bird_countries and use it for the update (and drop it later), but there might be a cleaner way. Thanks!

Answer 1

I think you want the nids that are unique, without duplicates. Try this:

SELECT nid
FROM bird_countries
GROUP BY nid
HAVING COUNT(*) = 1

Answer 2

After your edit, I see what you're trying to do

UPDATE table SET field=1 WHERE id in (SELECT nid FROM bird_countries GROUP BY nid HAVING count(nid) = 1); 

Answer 3

UPDATE table
SET field = 1
WHERE id IN (
    SELECT nid
    FROM bird_countries
    GROUP BY nid
    HAVING COUNT(nid) = 1
)

Answer 4

Edit: Following update to the question. This article suggests rewriting as a JOIN to avoid this issue. So does this syntax work?

update bird_countries as b
    inner join (
       SELECT nid FROM bird_countries group  by nid HAVING count(*) = 1
    ) as t on t.nid = b.nid
set season=1

Answer 5

I´m not sure if this is what you need, but, Are you trying to update a column based upon the nId?

If you need to do that you can do this:

update table 
set field=1 
FROM table 
inner join (SELECT count(nid) as total, nid
FROM bird_countries b
group by nid
order by total asc) as Table2
ON table.id=Table2.nid