1

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.

1
  • What are the real rules for ordering the output? Based on your example, ORDER BY type, choice works perfectly. If that is not adequate, please change your example to have a row that violates that ORDER BY.
    – Rick James
    Feb 24, 2021 at 21:55

1 Answer 1

1

You absolutely do not want to change the id for any row when inserting new data.

Your Primary Key (id) value should be set when each record is created, remain the same throughout the entire lifetime of that record, right up to the point where that record is finally destroyed.
Imagine if banks "renumbered" people's bank accounts every time someone [else] closed their account. There would be chaos.

If the combination of "choice" and "type" is unique, then that could form a Natural, Composite, Primary Key for this table, removing the need for the id column entirely. YMMV.
Alternatively, just let your database allocate ids to the new records - their actual values do not matter, just that they are unique.

I do not believe you need the order column either. From the data you show ...

select 
. . . 
order by type, choice

... would achieve exactly the same thing, without the need to update anything as data is added or removed.

2
  • Thanks for your help. Can you help me with that how can I achieve the updated table? This table will be modified by truncating the table and inserting all the row again. So I need to serialize both id and order. Thanks. Feb 24, 2021 at 14:36
  • If you're removing all the data and reinserting it all again, then your inserting logic should take care of the numbering. That said, I would still question why you are doing this at all! Forcibly sequential numbers are /not/ the norm in a Relational database. Database != Spreadsheet
    – Phill W.
    Feb 25, 2021 at 11:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.