Insert Table ID with 13,346 rows with LOOP

Navigation
0

I need to update a table with 13,000 rows. And this table contains a field called "track_cep_id" and all ids are 0. I need to update this with a loop so I do not duplicate the fields (EX: 1, 2, 3, 4 ...). Could someone help? 

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;
    
asked by anonymous 20.07.2017 / 08:12

2 answers

1

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

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

Example if the column already exists: 

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

Update:

Create the index: 

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

Then give the alter table by creating an auto-increment 

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 own database already inserts the numbering automatically for you. So you do not need to touch INSERT of yours.

    
20.07.2017 / 09:29
0

Rafael's answer already solves your problem. But if you just want to update the data without having to change the column type, follows 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
    
20.07.2017 / 20:02
Hide Carrousel images when opening on mobile and show others - Bootstrap 4 Include file in Java project