0

I have to update a super old database in oracle that didn't originally have a PK Id field. I need to populate this id field with 1,2,3,4 all with an SQL code and I can't think of how. Table name is Information and the new column is ID does anyone have an idea on how to do this. I have set the column to be incremental on trigger, but how do I update all the null values I have with inserting a new column?!

ALTER TABLE Information MODIFY ID INT IDENTITY(1,1) 
GO
CheezStix
  • 59
  • 2
  • 15
  • 1
    What you've shown isn't Oracle syntax. So what have you done - added a sequence and a trigger to set the (new?) column's value on insert from the sequence? What version of Oracle are you using? And does it matter which row gets which value? (Also: [see this](https://stackoverflow.com/q/43842064/266304), among others...) – Alex Poole Jun 02 '17 at 22:02
  • Well that makes sense on why it doesn't work! It doesn't matter how they get their values, just that is assigns 0-whatever to the Id column. I am not sure what oracle version it is... – CheezStix Jun 02 '17 at 22:08
  • I can't tell you how frustrated I am. – CheezStix Jun 02 '17 at 22:13
  • To find out what Oracle version you are running, execute `select * from v$version` and see what it shows. Include your version (all four parts, for example 12.1.0.4 - not just "Oracle 12") in all questions you post on this and similar discussion boards. –  Jun 02 '17 at 22:29
  • Apart from that, don't think of ID's as 1, 2, 3, 4; the primary key should be NOT NULL and unique, but there is no requirement that it should "have no gaps". Indeed, you will add more rows and delete older ones, so the ID's won't be "in sequence" even if you start it that way. –  Jun 02 '17 at 22:31
  • Alex if you can post that answer so I can give you a big hug and a thumbs up, it worked! – CheezStix Jun 02 '17 at 22:38
  • You can do two things now: "upvote" Alex's comment, and upvote the answer in Alex's thread (even if it's an old one; on Stack Overflow, good answers get more and more upvotes all the time). –  Jun 02 '17 at 22:40

0 Answers0