Update a column using stored procedure in PostgreSQL

Question

I got a table in which a column named items and its value is '' which is around 1000 of rows, how can we query in PostgreSQL using stored procedure to rename each items to a unique value like column with an incremental value i.e

 items0001, items0002, items003,.....items1000

Tried this but its not working

UPDATE product SET items=CASE
          WHEN items='' THEN  items='items'+1
          ELSE items123

          END

Have you tried something? If yes then, what have you tried?

keyser
– keyser

2012-10-09 10:19:28 +00:00
Commented Oct 9, 2012 at 10:19 — keyser
– keyser, Commented Oct 9, 2012 at 10:19

score 1 · Accepted Answer · 2012-10-09 13:54:46Z

1

Assuming your table has a primary key column named id the following should do it:

update product
    set items = new_items.new_item
from (
  select id, coalesce(items,'')||to_char(row_number() over (), 'FM00009') as new_item
  from product
) as new_items
where new_items.id = p.id
and product.items is null or product.items = '';

edited Oct 9, 2012 at 13:54

answered Oct 9, 2012 at 12:51

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

user330315 Over a year ago

@dude: you didn't mention anything regarding null in your question. see my edit

user330315 Over a year ago

@dude: apparently you are using an outdated version of Postgres (probably 8.3 or even older).

Aditya Pratap Singh · Accepted Answer · 2012-10-09 10:24:34Z

0

UPDATE product SET items=CASE
      WHEN len(items)>0 THEN  items='items'+1
      ELSE items123

      END

answered Oct 9, 2012 at 10:24

Aditya Pratap Singh

1082 silver badges8 bronze badges

2 Comments

Mahmoud Gamal Over a year ago

The OP wants to update that column with an incremental value i.e items0001, items0002, items003, ...

dude Over a year ago

but i got items like tanker,puzzles etc in the column name... i just want to update the row which has a value null

DiaMonD · Accepted Answer · 2012-10-09 10:27:45Z

0

UPDATE product SET items=CASE WHEN len(items)>0 THEN 'items' || somesequence.nextval ELSE items END

answered Oct 9, 2012 at 10:27

DiaMonD

1214 silver badges14 bronze badges

Collectives™ on Stack Overflow

Update a column using stored procedure in PostgreSQL

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related