1

I have this random table with random contents.

id | name| mission
1 | aaaa | kitr
2 | bbbb | etre
3 | ccccc| qwqw
4 | dddd | qwert
5 | eeee | potentials
6 | ffffffff | toto


What I want is to add in the above table a column with id=3 with different name and different mission BUT the OLD id =3 I want to have an id = 4 with the name and the mission that it had before when it was id=3, and the OLD id =4 become id=5 with the name and mission of id 5 and so on.
its like i want to enter a column inside of the columns and the below column i want to increase there id +1 but the columns rest the same. example below:


id | name| mission
1 | aaaa | kitr
2 | bbbb | etre
3 | zzzzzz| zzzzz
4 | ccccc| qwqw
5 | dddd | qwert
6 | eeee | potentials
7 | ffffffff | toto

why I want to do this ? I have a table that has 2 CLOB. Inside of those CLOBS there are different queries ex: id =1 has clob of creation of a table id=2 inserts for the columns id=3 has creation of another table id=4 has functions
if you add all of this id in one text(or clob) they will have to create then inserts then create then functions. that table it is like a huge script .
Why I am doing this ? The developers are building their application and they want the sql to work in specific order and I have 6 developers and am organizing the data modeling and the performance and how the scripts are running .So the above table is to organize the calling of the scripts that they wany

Community
  • 1
  • 1
MMD
  • 115
  • 2
  • You may want to use numbers like 10/20/30 instead of 1/2/3. Then you can insert values without re-numbering everything. – Jon Heller May 27 '13 at 16:20

2 Answers2

7

Simply put, don't do it.

This case highlights why you should never use any business value, i.e. any 'real world values' for a Primary Key.

In your case I would recommend primary keys not be used for any other purposes. I recommend you add an extra column 'order' and then change THAT column in order to re-order the rows. That way your primary key and all the other records will not need to be touched.

This avoid the issue that your approach would need to change ALL the database records below the current record which seems like a really bad approach. Just imagine trying to undo that update ;)

Some more info here: https://stackoverflow.com/a/8777574/631619

Community
  • 1
  • 1
Michael Durrant
  • 93,410
  • 97
  • 333
  • 497
  • it took me to insert a new column to understand your point 100%, we use Pk alot of time but it seems it has alot of powers. I cant vote up but I will say thank you alot for your informations :) – MMD May 24 '13 at 20:12
1
UPDATE random_table r1
SET id =
  (SELECT CASE WHEN id > 2 THEN id+1 ELSE id END id FROM random_table r2
  WHERE r1.mission=r2.mission
  )

Then insert the new value.

Mukesh Dhiman
  • 11
  • 1