How to change the last 100 records of the table in the PostgreSQL database?

Navigation
2

I need to change a table field in my database to perform some tests, and would like to update only the last 100 records in the table. I already tried this code: UPDATE titulo SET autorizado='S' ORDER BY id_titulo DESC LIMIT 100; More this code returns me an error in ORDER I could not understand why. If anyone has any other alternative how to do this and want to share I thank you right away if you can help me.

    
asked by anonymous 02.06.2016 / 14:56

2 answers

3

UPDATE operations do not receive ORDER BY parameters. You need to select the records first: 

UPDATE titulo SET autorizado='S' WHERE id_titulo IN
(SELECT id_titulo FROM titulo ORDER BY id_titulo DESC LIMIT 100);
    
02.06.2016 / 15:09
2

The UPDATE does not have LIMIT so it thinks that it is continuity of ORDER BY that also is not accepted.

Try this: 

UPDATE titulo SET autorizado = 'S' FROM (
    SELECT id_titulo FROM titulo ORDER BY id_titulo DESC LIMIT 100 FOR UPDATE) subquery
    WHERE id.titulo = subquery.id_titulo;

This will make the selection you want and apply the update to it.

FOR UPDATE can be discarded if you do not need competition. Just do this if you're sure there will be no problem.

    
02.06.2016 / 15:09
Concatenating Javascript Object String Validations