CODEWITHSUNDEEP
sqlpostgresql
user10457989
user10457989

Reputation: 73

Select and update rows in table while incrementing values

I have a database table that I’m adding a sort_order column to. I want to iterate over each row in the table and set the sort_order while incrementing it’s value by 1.

Pseudo:

UPDATE captions set sort_order++ where category_id = ?

What is a way I could achieve this?

Upvotes: 0

Views: 37

Answers (3)

Zack
Zack

Reputation: 2341

Alternatively, you can use the ROW_NUMBER window function:

WITH caption_rn AS
(
    SELECT *, ROW_NUMBER() OVER (ORDER BY caption_id) AS rn
    FROM captions
)
UPDATE captions
SET sort_order = caption_rn.rn
FROM caption_rn
WHERE captions.caption_id = caption_rn.caption_id

Upvotes: 0

AmeyaN99
AmeyaN99

Reputation: 172

DECLARE
  v_sort_order NUMBER := 1;
BEGIN
  FOR i IN
  ( SELECT category_id FROM captions ORDER BY <column names>
  );
  LOOP
    UPDATE captions
    SET sort_order    = v_sort_order
    WHERE category_id = i.category_id;
    v_sort_order     := v_sort_order +1;
  END LOOP;
EXCEPTION
WHEN OTHERS THEN
  raise_application_error(-20501,'error'||SQLCODE||sqlerrm);
END;

This PL-SQL block should do the thing.

FOR i IN ( SELECT category_id FROM captions ORDER BY ); this will select all your columns of captions table.

In order by clause replace with actual column names u want to sort with. update statement will update all the rows selected.

Upvotes: 0

The Impaler
The Impaler

Reputation: 48810

You can use a sequence:

create sequence my_seq1;

UPDATE captions set sort_order = nextval('my_seq1') where category_id = ?

Upvotes: 1

Related Questions