How to limit rows in PostgreSQL update statement

Question

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?

Answer 1

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);

Answer 2

If the exact number of rows updated is not critical and the goal is just to keep the transaction durations brief 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