How to increment a column value by 1 for selected rows in postgres?

Question

I have a postgres table which contains the columns id, name, item and counter.Now I want to increment the counter value by 1 for those rows that the user belongs to.

I tried doing something like this but I get syntax error

UPDATE Items set counter = counter + 1 WHERE users = 'Tony';

This should increment the counter value by 1 for all the rows that the user Tony belongs to.Something like

initial

id | name | item    | counter
1  | Tony  | car    | 1
2  | Tony  | bike   | 1
3  | Ray   | car    | 1
4  | Ray   | bike   | 1
5  | Tony  | cycle  | 1

final

id | name | item    | counter
1  | Tony  | car    | 2
2  | Tony  | bike   | 2
3  | Ray   | car    | 1
4  | Ray   | bike   | 1
5  | Tony  | cycle  | 2

But I get syntax error for my query.What am I doing wrong?

Perhaps because your table has a column called name but not one called user. — Gordon Linoff
– Gordon Linoff, Commented Sep 28, 2018 at 14:56

S-Man · Accepted Answer · 2018-09-28 14:59:20Z

10

Your UPDATE query contains users instead of name.

demo: db<>fiddle

UPDATE Items set counter = counter + 1 WHERE name = 'Tony';

answered Sep 28, 2018 at 14:59

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

Comments

Eugene · Accepted Answer · 2018-09-28 14:58:19Z

1

Probably column name was wrong. try this:

UPDATE Items set counter = counter + 1 WHERE name= 'Tony';

answered Sep 28, 2018 at 14:58

Eugene

1,54310 silver badges23 bronze badges

Comments

sunil kumar · Accepted Answer · 2021-05-27 09:47:22Z

FOR rowdata1 IN

    select   employee_id, accrual_group_code, accrual_type, start_date,accru_date from z_123  where (doc_id =0 or doc_id is null ) order by accru_date
            LOOP
                            employeeId   = rowdata1.employee_id;
                            accrualgroupcode   = rowdata1.accrual_group_code;
                            accrualtype   = rowdata1.accrual_type;
                            accrudate   = rowdata1.accru_date;
                            select (cast(date_trunc('month', accrudate::date) as date) - interval '1 month')::date into startdate;

                            update  z_123 set company_id = 2 , doc_id= 2 where employee_id = employeeId
                            and  accrual_group_code = accrualgroupcode and  accrual_type = accrualtype and accru_date = accrudate;                                
            END LOOP;

Collectives™ on Stack Overflow

How to increment a column value by 1 for selected rows in postgres?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related