1

My table employees contain more than ten million data. i want to update 10k rows in commission_pct column where commission_pct is null and set zero(0) value.

in oracle I can easily solve this by using rownum.

update employees
set commission_pct=0
where commission_pct is null and rownum<=10000;

but postgresql does not support rownum.

how to solve this in postgresql?

6

You need to search for the desired rows with a subquery, and use the primary key of the table to relate those rows to the table in the UPDATE statement.

In general, rownum can be replaced with the row_number() window function (see, e.g., Using window functions in an update statement), but for this case, it is easier to just use limit:

UPDATE employees
SET commission_pct = 0
WHERE id IN (SELECT id
             FROM employees
             WHERE commission_pct IS NULL
             LIMIT 10000);
2

If the exact number of rows updated is not critical and the goal is just to keep the transactions short you can use a random expression or an expression involving some uniformly distributed column to limit the number of rows updated.

These expressions will need to be modified when the query is repeated.

values of id.

UPDATE employees
SET commission_pct = 0
WHERE commission_pct is null and id < 100000

mod 100 of id

UPDATE employees
SET commission_pct = 0
WHERE commission_pct is null and id % 100 = 0

random selection

UPDATE employees
SET commission_pct = 0
WHERE commission_pct is null and random() < 0.01

"birthday"

UPDATE employees
SET commission_pct = 0
WHERE commission_pct is null and 
 day_of_birth - date_trunc(year,day_of_birth)::date = 0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.