How to Update a group of rows

Question

My sqlfiddle: http://sqlfiddle.com/#!15/4f9da/1

I'm really bad explaining this and noob to do complex query(just the basics), because its complicated.

Situation: The column revision is a group of the same object related, for example: ids 1 2 3 are the same object and always refering the last old object on using id to ground_id.

Problem: I need to make ord column to make same id for the same group of object. example: the ids 1 2 3 need their value setted to 1, because the revison 0 is the id 1. Same for id 4, which must have ord 4 and id 5 too.

Basically must be like this:

Patrick · Accepted Answer · 2016-01-23 03:07:38Z

You need a recursive query to do this. First you select the rows where ground_id IS NULL, set ord to the value of id. In the following iterations you add more rows based on the value of ground_id, setting the ord value to that of the row it is being matched to. You can then use that set of rows (id, ord) as a row source for the UPDATE:

WITH RECURSIVE set_ord (id, ord) AS ( SELECT id, id FROM ground WHERE ground_id IS NULL UNION SELECT g.id, o.ord FROM ground g JOIN set_ord o ON o.id = g.ground_id ) UPDATE ground g SET ord = s.ord FROM set_ord s WHERE g.id = s.id;

(SQLFiddle is currently not-responsive so I can't post my code there)

Do you know what it changes from postgres 8? On 9 works fine, but on 8 gives me error from the syntax UPDATE. I saw on docs and theres no difference.
The recursive query with UPDATE is only available since version 9.1. But you really shouldn't be using such an old version anymore; 8.4 was at end of support by July 2014. The 8.4 docs do not show the option with the recursive query.
I know, In my dev it is 9.4 but when I checked on production, it is 8.14.4. Going migrate it.

Collectives™ on Stack Overflow

How to Update a group of rows

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related