How to mass update table

Question

Suppose, I have a table that looks like this:

+----+-----------+------+-------+--+
| id | Part      | Seq  | Model |  |
+----+-----------+------+-------+--+
| 1  | Head      | 0    | 3     |  |
| 2  | Neck      | 1    | 3     |  |
| 3  | Shoulders | 11   | 3     |  |
| 4  | Groin     | 2    | 3     |  |
| 5  | Stomach   | 5    | 3     |  |
+----+-----------+------+-------+--+

As you can see the Seq field is the order these items will display on the front end. Here is the Seq 0, 1, 2, 5, 11. Now the user wants to reorder the list. If they want Stomach (id: 5) to have seq: 0 how would I write a query to update all the Seq values for only Model 3?

Do you want to make Stomach the first item, and move the others (increment by one), or swap with the currently first, or something else? — Stefan Steinegger, Feb 15 '16 at 13:58
Yes, I want Stomach to be first one and others shifted down. — Luke101, Feb 15 '16 at 13:58
Perhaps: http://stackoverflow.com/questions/8607998/using-a-sort-order-column-in-a-database-table/8608085#8608085 — xQbert, Feb 15 '16 at 13:58
In your example your seq column behaves like a sorted string. If it uses a string write '01' into the field and leave the rest untouched. — Ralf, Feb 15 '16 at 13:59

score 0 · Answer 1 · answered Feb 15 '16 at 13:58

Here is one method that reassigns the sequence numbers as ordered integers, with "Stomach" first:

with toupdate as (
      select t.*,
             row_number() over (partition by model -- not really necessary
                                order by (case when Part = 'Stomach' then -1
                                               else seq
                                          end)
                               ) as newseq
      from t
      where model = 3
     )
update toupdate
    set seq = newseq;

How to mass update table

1 Answers1