CODEWITHSUNDEEP
mysqlsqldatabasesql-insert
HiddenHopes
HiddenHopes

Reputation: 63

How to insert rows for each of the TYPE column?

How to insert rows for each of the type column? If there is two type, type 1 and type 2, then I need to insert two rows and also need to change the order and id value for whole table.

current status:

CHOICE Table

id  choice  type    order
1    AA      1       1
2    BB      1       2
3    CC      1       3
4    AAA     2       4
5    BBB     2       5
6    CCC     2       6
7    DDD     2       7

Required updated table: Now i wan to insert choice "000" for each type. The updated table will be look like bellow. How can I achieve this?

updated CHOICE Table

id  choice  type    order
1    000     1       1
2    AA      1       2
3    BB      1       3
4    CC      1       4
5    000     2       5
6    AAA     2       6
7    BBB     2       7
8    CCC     2       8
9    DDD     2       9

here, id and order column serialized again.

The actual table is too big, so I cannot insert by edit. Please help for this complex query. I have no clue to solve this.

Upvotes: 0

Views: 33

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Use insert . . . select to insert the rows:

insert into choice (choice, type)
    select distinct '000', type
    from choice;

This assumes that id is automatically assigned (and it will be different from your example).

However, it looks like you want to update the order as well. For this, I would suggest an update:

update choice c join
       (select c2.*,
               row_number() over (partition by choice order by (order is null) desc, order) as new_order
        from choice c2
       ) c2
       on c.id = c2.id
    set c.order = c2.new_order;

As an editorial comment, order is a very bad choice for a column name because it is a SQL keyword.

Upvotes: 1

Related Questions