CODEWITHSUNDEEP
sqlpostgresqlplpgsqlwindow-functionspostgresql-9.3
Peter Sun
Peter Sun

Reputation: 1813

Update every N rows with an increment of 1

I am an SQL server developer working on a project in a PostgreSQL environment. I am having some PostgreSQL syntax issues. I am working off version 9.3.

In a given table, I am trying to set every 10 rows an increment of 1. Example:

 CREATE TABLE x_list(
    id varchar(100),
    field1 varchar(20)
)

In the example, I have 100 rows. For the first 10 rows, the field1 value is '0001'. For the next 10 rows, the field1 value is '0002'. For the next 10 rows, the field1 value is '0003' and so on.

This is what I have so far:

DECLARE 
    lp_cnt INTEGER := 1;
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM x_list WHERE field1 IS NULL) THEN

    UPDATE x_list
    SET field1 = RIGHT('0000' + lp_cnt::VARCHAR, 4)
    WHERE id IN (SELECT id FROM x_list WHERE field1 IS NULL LIMIT 10)
        
        lp_cnt := lp_cnt + 1
    END IF;
END $$;

I am getting the following error:

ERROR:  syntax error at or near "INTEGER"
LINE 2:  lp_cnt INTEGER := 1;
                ^
********** Error **********

ERROR: syntax error at or near "INTEGER"
SQL state: 42601
Character: 18

I am not sure how to solve the error. I tried replacing INTEGER with INT I am also open to a better way to accomplish my goal.

Upvotes: 1

Views: 74

Answers (1)

Zegarek
Zegarek

Reputation: 26113

You can handle this using a plain, non-procedural-SQL update with a CTE, a row_number() window function and / integer division.
demo at db-fiddle

with cte as (
  select ctid
       , 1+(row_number()over(order by id::int)-1)/10 as rn
  from x_list)
update x_list
set field1=lpad(rn::text,4,'0')
from cte
where x_list.ctid=cte.ctid;

This uses a hidden system column ctid instead of id because according to your table definition, id isn't necessarily unique. Also, I'm using lpad() for left-padding the zeroes. The added and subtracted 1's are there to start from 1, not 0, and to increment after 10's, not 9's.

Although a bit more slowly, your idea could still work if you fix it:

  1. There are missing semicolons in your example.
  2. You probably wanted to loop for as long as there are things left to update.
  3. As already pointed out by @JohnH, declare belongs inside the do block.
  4. You don't need a column inside the exists() subquery - it only checks if any rows are returned but all their columns are discarded.

demo at db-fiddle

DO $d$
DECLARE lp_cnt INTEGER := 1;
BEGIN
    WHILE EXISTS (SELECT FROM x_list WHERE field1 IS NULL) LOOP

    UPDATE x_list
    SET field1 = RIGHT('0000' ||lp_cnt::text, 4)
    WHERE id IN (SELECT id FROM x_list 
                 WHERE field1 IS NULL 
                 ORDER BY id::int LIMIT 10);
        
    lp_cnt := lp_cnt + 1;
    END loop;
END $d$;

select*from x_list order by id::int limit 11;
id field1
1 0001
2 0001
3 0001
4 0001
5 0001
6 0001
7 0001
8 0001
9 0001
10 0001
11 0002

Consider an upgrade: PostgreSQL 9.3 support ended in 2018.

Upvotes: 2

Related Questions