Insert ID into Table with 13,346 lines with LOOP

Asked

Viewed 324 times

0

I need to update a table with 13,000 lines. And this table contains a field called "faixa_cep_id" and all ids are 0. I need to update this with a loop to not duplicate the fields (EX: 1, 2, 3, 4...). Someone could help me out?

DROP TABLE IF EXISTS `pl_joseanmatias_faixa_cep_peso`;
CREATE TABLE `pl_joseanmatias_faixa_cep_peso` (
  `faixa_cep_id` int(11) NOT NULL,
  `title` varchar(150) NOT NULL,
  `weight_min` float NOT NULL,
  `weight_max` float NOT NULL,
  `postcode_min` int(11) NOT NULL,
  `postcode_max` int(11) NOT NULL,
  `cost` float NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
sql database

  • Does the data you want to enter come from another table? If so, is there a relationship between the table you want to change and the table where the data is? Your question has become vague, specify with an example.

  • @Rafaelsalomão does not... I want you to start inserting ids automatically by counting from 1. Getting: 1 2 3 4 But without interfering with the other fields in the table...

  • Ahhhh, do you want an auto-increment field? That is, faixa_cep_id account from 1 to 13 thousand correct?

  • @Raphael Read this :D

  • @Rafaelsalomão made a mistake. I tried to mark you down there but I couldn’t

  • @Rafaelsalomão updated the post with an sql for table creation

  • @Rafaelsalomão and how I do it?

  • I don’t see auto-increment column in your table. Did you set the attribute : faixa_cep_id as index? It has to be an index of your table. I posted down there how it creates the Dice.

  • @Rafaelsalomão #1062 - Duplicate '0' input for faixa_cep_id key'

  • Because the entry I have set is of type Dice unico and its column contains duplicate values. create a single entry : "ALTER TABLE faixa_cep_id ADD INDEX ID (ID); " also works.

Show 5 more comments

2 answers

1


To add a new attribute to your table and set it as auto-increment you can run a ALTER TABLE thus : 

ALTER TABLE `nome_tabela`
ADD COLUMN `faixa_cep_id` INT(10) NOT NULL AUTO_INCREMENT FIRST;

Example if column already exists : 

ALTER TABLE `nome_tabela`
CHANGE COLUMN `faixa_cep_id` `faixa_cep_id` INT(10) NOT NULL AUTO_INCREMENT FIRST;

Updating :

Create the index :

ALTER TABLE `pl_joseanmatias_faixa_cep_peso`
ADD UNIQUE INDEX `faixa_cep_id` (`faixa_cep_id`);

Then give the alter table creating an auto-increment attribute

ALTER TABLE `pl_joseanmatias_faixa_cep_peso`
CHANGE COLUMN `faixa_cep_id` `faixa_cep_id` INT(10) NOT NULL AUTO_INCREMENT FIRST;

PS: With each new line the database itself automatically inserts the numbering for you. So you don’t need to touch any INSERT your.

  • ALTER TABLE pl_joseanmatias_faixa_cep_peso ADD COLUMN faixa_cep_id INT(10) NOT NULL AUTO_INCREMENT FIRST Mysql messages : Documentation #1075 - Incorrect table definition. Only a single auto-incremented field is allowed and it has to be set as key

  • appeared the following error: #1075 - Incorrect table definition. Only a single auto-incremented field is allowed and it has to be set as key

  • So you gave this because in your table there is already a field defined as auto-increment. And it only accepts an attribute with auto-increment. In this case post the table structure I will mount a querie that does this.

  • I was able to create the Dice like this: CREATE INDEX id_faixa_de_cep ON pl_joseanmatias_faixa_cep_peso (faixa_cep_id); E I surrounded the change column... Thank you so much for helping @Rafaelsalomão :D

0

Rafael’s answer already solves your problem. But if you just want to update the data without having to change it column type, follow an example:

declare @i int
set @i =0
while @i < 13346
begin
BEGIN TRAN

update  pl_joseanmatias_faixa_cep_peso set faixa_cep_id = SELECT @i + 1
COMMIT TRAN;
set @i =@i + 1
end

  • I made it through autoincrement... Thank you very much, @Marcosmarques ^^

Browser other questions tagged sql database

You are not signed in. Login or sign up in order to post.