Essex
Reputation: 6138
Increment value in MySQL
I have a MySQL table which looks like :
mysql> select * from llx_societe limit 10 ;
+-------+-----------------------------------------------+------------+--------+---------+---------+--------+--------+---------------------+-------+--------+-------------+------------------+-------------+-------------------------+-----------------------------+-------+------------------+----------------+---------+-----------+------+------+-------+-------+-------------+-----------+--------------------+-------------+-------+-------+------+---------+---------+---------+-----------+---------+-----------+--------------+-------------+-----------+-------------+--------+-------------+------------------+------------------+--------------+--------------------+--------------+---------------+---------------+---------------+---------------+---------------+----------------+----------------+-------------------------+-------------------------+--------------------+-----------+-----------------+-----------------+-----------------+-----------------+---------+-----------------+-------------+-------------------+--------------+------+--------+----------------+-----------------+-----------------+------------------+--------------------+
| rowid | nom | name_alias | entity | ref_ext | ref_int | statut | parent | tms | datec | status | code_client | code_fournisseur | code_compta | code_compta_fournisseur | address | zip | town | fk_departement | fk_pays | phone | fax | url | email | skype | fk_effectif | fk_typent | fk_forme_juridique | fk_currency | siren | siret | ape | idprof4 | idprof5 | idprof6 | tva_intra | capital | fk_stcomm | note_private | note_public | model_pdf | prefix_comm | client | fournisseur | supplier_account | fk_prospectlevel | fk_incoterms | location_incoterms | customer_bad | customer_rate | supplier_rate | fk_user_creat | fk_user_modif | remise_client | mode_reglement | cond_reglement | mode_reglement_supplier | cond_reglement_supplier | fk_shipping_method | tva_assuj | localtax1_assuj | localtax1_value | localtax2_assuj | localtax2_value | barcode | fk_barcode_type | price_level | outstanding_limit | default_lang | logo | canvas | import_key | webservices_url | webservices_key | fk_multicurrency | multicurrency_code |
+-------+-----------------------------------------------+------------+--------+---------+---------+--------+--------+---------------------+-------+--------+-------------+------------------+-------------+-------------------------+-----------------------------+-------+------------------+----------------+---------+-----------+------+------+-------+-------+-------------+-----------+--------------------+-------------+-------+-------+------+---------+---------+---------+-----------+---------+-----------+--------------+-------------+-----------+-------------+--------+-------------+------------------+------------------+--------------+--------------------+--------------+---------------+---------------+---------------+---------------+---------------+----------------+----------------+-------------------------+-------------------------+--------------------+-----------+-----------------+-----------------+-----------------+-----------------+---------+-----------------+-------------+-------------------+--------------+------+--------+----------------+-----------------+-----------------+------------------+--------------------+
| 5703 | A.D.P.N. | NULL | 1 | NULL | NULL | 0 | NULL | 2016-10-25 11:51:59 | NULL | 1 | 1 | NULL | NULL | NULL | 5 rue des Narcisses | 67116 | REICHSTETT | NULL | 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | 1 | 0 | NULL | NULL | NULL | NULL | 0 | 0 | 0 | 1 | NULL | 0 | NULL | NULL | NULL | NULL | NULL | 1 | 0 | NULL | 0 | NULL | NULL | 0 | NULL | NULL | fr_FR | NULL | NULL | 20161025092708 | NULL | NULL | NULL | NULL |
| 5704 | A.P.E.L.E | NULL | 1 | NULL | NULL | 0 | NULL | 2016-10-25 09:27:08 | NULL | 1 | 2 | NULL | NULL | NULL | 0 | 67000 | Adresse ? | NULL | 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | 1 | 0 | NULL | NULL | NULL | NULL | 0 | 0 | 0 | 1 | NULL | 0 | NULL | NULL | NULL | NULL | NULL | 1 | 0 | NULL | 0 | NULL | NULL | 0 | NULL | NULL | fr_FR | NULL | NULL | 20161025092708 | NULL | NULL | NULL | NULL |
| 5705 | A.Sauv.Nat. BRUMATH & environs KAPFER Gérard | NULL | 1 | NULL | NULL | 0 | NULL | 2016-10-25 09:27:08 | NULL | 1 | 4 | NULL | NULL | NULL | 2 rue du Maire Cornélius | 67170 | BRUMATH | NULL | 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | 1 | 0 | NULL | NULL | NULL | NULL | 0 | 0 | 0 | 1 | NULL | 0 | NULL | NULL | NULL | NULL | NULL | 1 | 0 | NULL | 0 | NULL | NULL | 0 | NULL | NULL | fr_FR | NULL | NULL | 20161025092708 | NULL | NULL | NULL | NULL |
| 5706 | A.S.P.E.E. A. GREINER/MAGNIETTE | NULL | 1 | NULL | NULL | 0 | NULL | 2016-10-25 09:27:08 | NULL | 1 | 5 | NULL | NULL | NULL | 27 rue des Alliés | 67114 | Eschau | NULL | 1 | 388685993 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | 1 | 0 | NULL | NULL | NULL | NULL | 0 | 0 | 0 | 1 | NULL | 0 | NULL | NULL | NULL | NULL | NULL | 1 | 0 | NULL | 0 | NULL | NULL | 0 | NULL | NULL | fr_FR | NULL | NULL | 20161025092708 | NULL | NULL | NULL | NULL |
| 5707 | AAPP de Burnaupt HENN René | NULL | 1 | NULL | NULL | 0 | NULL | 2016-10-25 09:27:08 | NULL | 1 | 6 | NULL | NULL | NULL | 12 rue de l' Etang | 68520 | BURNHAUPT LE BAS | NULL | 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | 1 | 0 | NULL | NULL | NULL | NULL | 0 | 0 | 0 | 1 | NULL | 0 | NULL | NULL | NULL | NULL | NULL | 1 | 0 | NULL | 0 | NULL | NULL | 0 | NULL | NULL | fr_FR | NULL | NULL | 20161025092708 | NULL | NULL | NULL | NULL |
| 5708 | ABEGG - LITZLER Jacques & Martine | NULL | 1 | NULL | NULL | 0 | NULL | 2016-10-25 09:27:08 | NULL | 1 | 7 | NULL | NULL | NULL | 16 rue du Muhlberg | 68730 | BLOTZHEIM | NULL | 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | 1 | 0 | NULL | NULL | NULL | NULL | 0 | 0 | 0 | 1 | NULL | 0 | NULL | NULL | NULL | NULL | NULL | 1 | 0 | NULL | 0 | NULL | NULL | 0 | NULL | NULL | fr_FR | NULL | NULL | 20161025092708 | NULL | NULL | NULL | NULL |
| 5709 | ABT Clémence | NULL | 1 | NULL | NULL | 0 | NULL | 2016-10-25 09:27:08 | NULL | 1 | 8 | NULL | NULL | NULL | 11 rue de Kembs | 68100 | Mulhouse | NULL | 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | 1 | 0 | NULL | NULL | NULL | NULL | 0 | 0 | 0 | 1 | NULL | 0 | NULL | NULL | NULL | NULL | NULL | 1 | 0 | NULL | 0 | NULL | NULL | 0 | NULL | NULL | fr_FR | NULL | NULL | 20161025092708 | NULL | NULL | NULL | NULL |
| 5710 | ACKER Daniel | NULL | 1 | NULL | NULL | 0 | NULL | 2016-10-25 09:27:08 | NULL | 1 | 9 | NULL | NULL | NULL | 68 rue J. Kablé | 67000 | Strasbourg | NULL | 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | 1 | 0 | NULL | NULL | NULL | NULL | 0 | 0 | 0 | 1 | NULL | 0 | NULL | NULL | NULL | NULL | NULL | 1 | 0 | NULL | 0 | NULL | NULL | 0 | NULL | NULL | fr_FR | NULL | NULL | 20161025092708 | NULL | NULL | NULL | NULL |
| 5711 | ACKER Emmanuel | NULL | 1 | NULL | NULL | 0 | NULL | 2016-10-25 09:27:08 | NULL | 1 | 10 | NULL | NULL | NULL | 34 rue de Soultz | 67100 | STRASBOURG | NULL | 1 | 388846074 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | 1 | 0 | NULL | NULL | NULL | NULL | 0 | 0 | 0 | 1 | NULL | 0 | NULL | NULL | NULL | NULL | NULL | 1 | 0 | NULL | 0 | NULL | NULL | 0 | NULL | NULL | fr_FR | NULL | NULL | 20161025092708 | NULL | NULL | NULL | NULL |
| 5712 | ACKERMANN Marcelle | NULL | 1 | NULL | NULL | 0 | NULL | 2016-10-25 09:27:08 | NULL | 1 | 11 | NULL | NULL | NULL | 5 rue Mittelharth | 68000 | COLMAR | NULL | 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | 1 | 0 | NULL | NULL | NULL | NULL | 0 | 0 | 0 | 1 | NULL | 0 | NULL | NULL | NULL | NULL | NULL | 1 | 0 | NULL | 0 | NULL | NULL | 0 | NULL | NULL | fr_FR | NULL | NULL | 20161025092708 | NULL | NULL | NULL | NULL |
+-------+-----------------------------------------------+------------+--------+---------+---------+--------+--------+---------------------+-------+--------+-------------+------------------+-------------+-------------------------+-----------------------------+-------+------------------+----------------+---------+-----------+------+------+-------+-------+-------------+-----------+--------------------+-------------+-------+-------+------+---------+---------+---------+-----------+---------+-----------+--------------+-------------+-----------+-------------+--------+-------------+------------------+------------------+--------------+--------------------+--------------+---------------+---------------+---------------+---------------+---------------+----------------+----------------+-------------------------+-------------------------+--------------------+-----------+-----------------+-----------------+-----------------+-----------------+---------+-----------------+-------------+-------------------+--------------+------+--------+----------------+-----------------+-----------------+------------------+--------------------+
I would like to replace rowid = 5703 by 1, rowid = 5704 by 2 etc ..
In order to get the following table :
+-------+-----------------------------------------------+------------+--------+---------+---------+--------+--------+---------------------+-------+--------+-------------+------------------+-------------+-------------------------+-----------------------------+-------+------------------+----------------+---------+-----------+------+------+-------+-------+-------------+-----------+--------------------+-------------+-------+-------+------+---------+---------+---------+-----------+---------+-----------+--------------+-------------+-----------+-------------+--------+-------------+------------------+------------------+--------------+--------------------+--------------+---------------+---------------+---------------+---------------+---------------+----------------+----------------+-------------------------+-------------------------+--------------------+-----------+-----------------+-----------------+-----------------+-----------------+---------+-----------------+-------------+-------------------+--------------+------+--------+----------------+-----------------+-----------------+------------------+--------------------+
| rowid | nom | name_alias | entity | ref_ext | ref_int | statut | parent | tms | datec | status | code_client | code_fournisseur | code_compta | code_compta_fournisseur | address | zip | town | fk_departement | fk_pays | phone | fax | url | email | skype | fk_effectif | fk_typent | fk_forme_juridique | fk_currency | siren | siret | ape | idprof4 | idprof5 | idprof6 | tva_intra | capital | fk_stcomm | note_private | note_public | model_pdf | prefix_comm | client | fournisseur | supplier_account | fk_prospectlevel | fk_incoterms | location_incoterms | customer_bad | customer_rate | supplier_rate | fk_user_creat | fk_user_modif | remise_client | mode_reglement | cond_reglement | mode_reglement_supplier | cond_reglement_supplier | fk_shipping_method | tva_assuj | localtax1_assuj | localtax1_value | localtax2_assuj | localtax2_value | barcode | fk_barcode_type | price_level | outstanding_limit | default_lang | logo | canvas | import_key | webservices_url | webservices_key | fk_multicurrency | multicurrency_code |
+-------+-----------------------------------------------+------------+--------+---------+---------+--------+--------+---------------------+-------+--------+-------------+------------------+-------------+-------------------------+-----------------------------+-------+------------------+----------------+---------+-----------+------+------+-------+-------+-------------+-----------+--------------------+-------------+-------+-------+------+---------+---------+---------+-----------+---------+-----------+--------------+-------------+-----------+-------------+--------+-------------+------------------+------------------+--------------+--------------------+--------------+---------------+---------------+---------------+---------------+---------------+----------------+----------------+-------------------------+-------------------------+--------------------+-----------+-----------------+-----------------+-----------------+-----------------+---------+-----------------+-------------+-------------------+--------------+------+--------+----------------+-----------------+-----------------+------------------+--------------------+
| 1 | A.D.P.N. | NULL | 1 | NULL | NULL | 0 | NULL | 2016-10-25 11:51:59 | NULL | 1 | 1 | NULL | NULL | NULL | 5 rue des Narcisses | 67116 | REICHSTETT | NULL | 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | 1 | 0 | NULL | NULL | NULL | NULL | 0 | 0 | 0 | 1 | NULL | 0 | NULL | NULL | NULL | NULL | NULL | 1 | 0 | NULL | 0 | NULL | NULL | 0 | NULL | NULL | fr_FR | NULL | NULL | 20161025092708 | NULL | NULL | NULL | NULL |
| 2 | A.P.E.L.E | NULL | 1 | NULL | NULL | 0 | NULL | 2016-10-25 09:27:08 | NULL | 1 | 2 | NULL | NULL | NULL | 0 | 67000 | Adresse ? | NULL | 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | 1 | 0 | NULL | NULL | NULL | NULL | 0 | 0 | 0 | 1 | NULL | 0 | NULL | NULL | NULL | NULL | NULL | 1 | 0 | NULL | 0 | NULL | NULL | 0 | NULL | NULL | fr_FR | NULL | NULL | 20161025092708 | NULL | NULL | NULL | NULL |
| 3 | A.Sauv.Nat. BRUMATH & environs KAPFER Gérard | NULL | 1 | NULL | NULL | 0 | NULL | 2016-10-25 09:27:08 | NULL | 1 | 4 | NULL | NULL | NULL | 2 rue du Maire Cornélius | 67170 | BRUMATH | NULL | 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | 1 | 0 | NULL | NULL | NULL | NULL | 0 | 0 | 0 | 1 | NULL | 0 | NULL | NULL | NULL | NULL | NULL | 1 | 0 | NULL | 0 | NULL | NULL | 0 | NULL | NULL | fr_FR | NULL | NULL | 20161025092708 | NULL | NULL | NULL | NULL |
+-------+-----------------------------------------------+------------+--------+---------+---------+--------+--------+---------------------+-------+--------+-------------+------------------+-------------+-------------------------+-----------------------------+-------+------------------+----------------+---------+-----------+------+------+-------+-------+-------------+-----------+--------------------+-------------+-------+-------+------+---------+---------+---------+-----------+---------+-----------+--------------+-------------+-----------+-------------+--------+-------------+------------------+------------------+--------------+--------------------+--------------+---------------+---------------+---------------+---------------+---------------+----------------+----------------+-------------------------+-------------------------+--------------------+-----------+-----------------+-----------------+-----------------+-----------------+---------+-----------------+-------------+-------------------+--------------+------+--------+----------------+-----------------+-----------------+------------------+--------------------+
To update the first row, I can write :
UPDATE `dolibarr`.`llx_societe` SET `rowid` = '1' WHERE `llx_societe`.`rowid` = 5703;
But How I can make this operation for all rows ?
Thank you !
Upvotes: 0
Views: 84
Answers (4)
Eldy
Reputation: 508
Note that if you change id on table llx_societe, you will break all links in opther tables that point to this table (field fk_soc for example in table llx_propal, but a lot of tables are concerned)
Upvotes: 0
converset jordan
Reputation: 56
You can use a procedure and do a loop to start your row at 1 and increment next rows by 1 like this (don't know if it works but it's an idea) :
CREATE PROCEDURE rename()
BEGIN
DECLARE count,id INT default 0;
DECLARE cur1 CURSOR FOR SELECT MIN(rowid) FROM dolibarr.llx_societe;
DECLARE cur2 CURSOR FOR SELECT rowid FROM dolibarr.llx_societe;
OPEN cur1;
OPEN cur1;
set done = 0;
read_loop: LOOP
FETCH cur1 INTO count;
FETCH cur2 INTO id;
if done = 1 then leave read_loop; end if;
UPDATE `dolibarr`.`llx_societe` SET @id = @count + 1;
set count = count+1;
END LOOP;
CLOSE cur1;
CLOSE cur2;
END;
Upvotes: 1
dr fu manchu
Reputation: 698
Just substract the offset (5702) from all rows. Be sure to use an ORDER BY clause to prevent duplicate key errors.
UPDATE `dolibarr`.`llx_societe` SET `rowid` = `rowid` - 5702 ORDER BY `rowid`;
Upvotes: 2
Chris Lear
Reputation: 6742
Will this do?
UPDATE `dolibarr`.`llx_societe` SET `rowid` = `rowid` - 5702;
The assumption is that there aren't gaps in the id sequence.
Upvotes: 2
Related Questions
- MySQL incrementing value
- Increment value depending other one
- MySQL: Increment column by field
- Insert new increment value
- Increment int value in MySql
- MySQL increment value in a text field
- Increment string in MySQL
- how do you increment a field in mysql?
- How to increment text value in SQL
- Incrementing in MySQL