Number ranges Nuclet – subsequent renumbering of existing records via db statement. |
Language: Deutsch · English
Subsequent renumbering of existing records via DB statement – part of the Number ranges Nuclet documentation (renumbering existing records via a database statement).
On this page |
This page was machine-translated from German as a first draft and is currently under review. The authoritative source remains the German original (see the language switch above). |
For number ranges with a simple (non-date-specific) prefix.
Example of an adjustment with PostgreSQL via the query editor of pgAdmin.
set search_path to <schemaname>;
-- Sequenz erstellen
CREATE SEQUENCE meinenummer
INCREMENT 1
MINVALUE 1
MAXVALUE 9999999999 -- muss groß genug sein um alle Datensätze durchnummerieren zu können
START <Nächste zu vergebende Nummer>;
-- Datensätze durchnummerieren
WITH L AS (
SELECT intid, datcreated
FROM <Tabelle>
WHERE datcreated > make_date(<Jahr>, <Monat>, <Tag>) -- Durchnummerierung ab diesem Erstellungsdatum
ORDER BY datcreated
FOR UPDATE
)
UPDATE <Tabelle> SET <Nummernfeld> = '<MeinPrefix>' || NEXTVAL('meinenummer')::varchar(255)
FROM L
WHERE <Tabelle>.intid = L.intid;
-- Nummernkreis aktualiseren
UPDATE zz47_nummernkreise SET nextvalue = NEXTVAL('meinenummer')
WHERE name='<Nummernkreisname>';
-- Sequenz löschen
DROP SEQUENCE meinenummer;
-- Veränderte Datensätze anzeigen
SELECT intid, datcreated, <Nummernfeld>
FROM <Tabelle>
WHERE datcreated > make_date(<Jahr>, <Monat>, <Tag>)
ORDER BY datcreated ASC;
|