CODEWITHSUNDEEP
sqlpostgresql
fsi
fsi

Reputation: 1367

How to Update a group of rows

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:

enter image description here

Upvotes: 1

Views: 74

Answers (1)

Patrick
Patrick

Reputation: 32159

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)

Upvotes: 1

Related Questions