Andromedae93 Andromedae93 - 1 month ago 10
MySQL Question

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 !

Answer

Will this do?

UPDATE `dolibarr`.`llx_societe` SET `rowid` = `rowid` - 5702;

The assumption is that there aren't gaps in the id sequence.