how to update a row in postgresql using where and order

I have the table below named transactionCount:

 id | username_id | count | active 
---- ------------- ------- --------
  1 |           1 |     1 | t
  2 |           1 |     3 | t
  4 |           2 |     1 | t
  5 |           3 |     6 | f

I want to update the first row where username_id == 1 of column count.

Because of the way the data is structured, i must not used the id but rather find a way to ORDER_BY by username and update the first row of that user where active = t (true)

the final result should be:

 id | username_id | count | active 
---- ------------- ------- --------
  1 |           1 |     2 | t               <================ RESULT
  2 |           1 |     3 | t
  4 |           2 |     1 | t
  5 |           3 |     6 | f

I have tried the sql query below:

UPDATE transactionCount SET count = 2 WHERE username_id = 1 AND active = 'true' order_by id ASC;

but it does not seem to work, is it a better way to write that query?

CodePudding user response：

You need to find the ID to be updated using a subquery. That can then be used in the WHERE condition of your UPDATE statement:

UPDATE transactioncount 
  SET count = 2 
WHERE ID = (select min(id)
            from transactioncount 
            where username_id = 1 
              AND active = 'true');

This assumes that id is unique (e.g. the primary key)

CodePudding user response：

Updates should be performed through primary key fields.

Otherwise your tables can be screwed up since more than one record could match your filtering criteria.

In your case the topmost line you seek might be different than you have earlierly anticipated.

CodePudding user response：

UPDATE transactionCount SET count=2
WHERE ID =(SELECT id FROM transactionsCount WHERE username_id=1 AND active= ‘true’ ORDER BY id ASC LIMIT 1)

This will only take the first id found and update it