How to change the last 100 table records in the Postgresql database?

Asked

Viewed 754 times

2

I need to change a table field in my database to perform some tests, and would like to update only the last 100 table records. I’ve tried that code: UPDATE titulo SET autorizado='S' ORDER BY id_titulo DESC LIMIT 100; But this code returns me an error in ORDER I couldn’t figure out why. If anyone has any other alternative of how to do this and want to share I would appreciate if you can help me.

sql postgresql
  • 1

    Tell me what the mistake is.

  • ERROR: syntax error on or near "ORDER" SQL state: 42601 Character: 34

2 answers

5


Operations UPDATE do not receive parameters ORDER BY. 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);

  • thanks a lot for the help, it worked perfectly! vlw.

  • @Brunoduarte No, it’s always a pleasure to help!

2

The UPDATE has not LIMIT so he thinks that that’s continuity of ORDER BY which is also 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;

I put in the Github for future reference.

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

The FOR UPDATE can be discarded if you do not need competition. Only do this if you are sure that there will be no problem.

Browser other questions tagged sql postgresql

You are not signed in. Login or sign up in order to post.