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.
Best Answer
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 ...
... would achieve exactly the same thing, without the need to update anything as data is added or removed.