0

Is it possible to increase the PK somehow? I need to make it start at about 10000. Is there some sql statement I could execute directly in phpMyAdmin?

EDIT: I need to be a bit more clear...

I need to update all rows with a PK over 2190 (about 2000 rows). Need to update them to start at about 10000 instead... So I do believe it is some sort of programming question...

ThomasK
  • 2,210
  • 3
  • 26
  • 35
  • PK meaning primary key? How is it currently generated? If it's a sequence just redefine the sequence to start at your desired value. – Dave Richardson Oct 23 '12 at 08:11
  • PK meaning primary key indeed. And PK is (int) AI. What I need is to like update all of the existing ones. Not just start new ones at a higher number... – ThomasK Oct 23 '12 at 08:12
  • This question is not programming-related, so it doesn't belong on stackoverflow. But we have a sister site dba.stackexchange.com which is especially for database administration questions like this. – Philipp Oct 23 '12 at 08:13
  • Is the PK referenced by foreign keys in other tables? Or just a stand-along column you need to increase for some reaosn? – SchmitzIT Oct 23 '12 at 08:23
  • The PK is not linked to anything. I just need to update, and increase, the value of the records from 2190 and obove as a temporary fix. Data from two different tables have been inserted into a third one. – ThomasK Oct 23 '12 at 08:30
  • @ThomasK Updated my answer to reflect these changes. Word of advice - please make your question complete next time; you'll save time both of yours and fellow SOers trying to help you. – raina77ow Oct 23 '12 at 09:14
  • Voting to re-open. The "duplicate" is about new Primary Key values, this about modifying existing values. – S.L. Barth is on codidact.com Oct 23 '12 at 15:12

2 Answers2

4

Quoting the doc.

So the question is about updating some set of values. Well, here's one way to do it:

UPDATE some_table 
   SET some_pk = 10000 + some_pk 
 WHERE some_pk > 2190

Still, you have to follow this with ALTER TABLE tbl AUTO_INCREMENT = 10000; otherwise all the rows inserted after this UPDATE still get an old auto_increment. For example:

CREATE TABLE t (id INT AUTO_INCREMENT NOT NULL, s VARCHAR(5), PRIMARY KEY (id));
INSERT INTO t (s) VALUES ('a'), ('b'), ('c'), ('d'), ('e');

UPDATE t SET id = id + 10000 WHERE id > 3;
ALTER TABLE t AUTO_INCREMENT = 10000;

INSERT INTO t (s) VALUES ('f');

Without ALTER TABLE... 'f''s id will be 6; with it - 10006.

Here's SQL Fiddle illustrating this concept.

raina77ow
  • 103,633
  • 15
  • 192
  • 229
1

If your table has already been created with an auto-incr. index, use

ALTER TABLE tbl AUTO_INCREMENT = 10000;
Tobias
  • 7,238
  • 10
  • 46
  • 77