UPDATE using count?

1

HEI,
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.

tables.jpg1096×1071 95.2 KB

3

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

4

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
)

5

it wont let me use count with having or where.

6

Why?

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.

1 Like
7

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

8

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:

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