SQLTeam.com | Weblogs | Forums

UPDATE using count?


I need to update this the table PROJEKTI to say priorit='4' and sijainti=''OULU' where there is only one or two people working on that project (PROJ_HENK TABLE)
How can I get it to only update those records? PTUN is the projects.


It basically should change the p2 p3 and p6 to the 4 and oulu.


You can play with this to get it going!! Good luck!

--update i
--set i.priorit = '4', i.sijainti='OULU'
select i.*
from projekti i
where i.ptun in
select ptun from proj_henk (nolock) having count(ptun)>=1
group by ptun


it wont let me use count with having or where.



need Error message and also the actual code you are using, otherwise we are just wasting time guessing as to what the problem might be.


I figured it out. Was something simple like I thought it would be. It needed to be,

UPDATE projekti p
SET priorit='4' ,sijainti='OULU'
WHERE (SELECT COUNT(ph.ptun) FROM proj_henk ph
WHERE ph.ptun=p.ptun
GROUP BY ph.ptun)<=2

But I had been writing it
UPDATE projekti p
SET priorit='4' sijainti='OULU'
WHERE (SELECT COUNT(ph.ptun)<=2 FROM proj_henk ph
WHERE ph.ptun=p.ptun
GROUP BY ph.ptun)
all I needed to do was move the <=2 to outside the brackets.....something so simple lol


Hmmm ... I can't see anything wrong with your first one. Personally I would use a slightly different syntax, but maybe what you have is fine, I've just never tried that before :slight_smile:

SET ...
UPDATE projekti AS p
    JOIN ...