I want to have an sql update query statement that will update the number of items of table called pe-CodePudding

I want to have an sql update query statement that will update the number of items of a table called people that has more than 400 items and set them to 400.

the logic will look like this:

Given a org id

Find people with claps > 400

Update number to 400.

I tried this but did not work, because i need also need to check the org id.

update people set "availableClaps" = 400
where "availableClaps" > 400 in (select id from people)

CodePudding user response：

If "given a org id" means that you'll get it as a parameter, then you could

UPDATE people p
   SET p."availableClaps" = 400
 WHERE     p."availableClaps" > 400
       AND p.org_id = :par_org_id;           --> this

(:par_org_id represents a bind variable here; you might need to use something else, depending on how and where you get that value from.)

(BTW, if you use Oracle, it kind of hates mixed letter case and identifiers enclosed into double quotes; get rid of them, if you can. Otherwise you'll always have to reference them using double quotes and match letter case exactly as while creating the table.)

CodePudding user response：

Just pass in the orgId value (you can use a named bind variable :orgId or a static value, like 42):

UPDATE people
SET   "availableClaps" = 400
WHERE "availableClaps" > 400
AND   orgId = :orgId