recursief aanpassen van records

Beetje vage beschrijving, misschien kan je er een voorbeeld bij geven?

Algemeen gesproken is alles in SQL een tabel. Als je twee tabellen samenvoegt tot 1 resultaat met bijvoorbeeld een LEFT JOIN, dan is de uitkomst een resultaattabel. Die kan je weer opslaan in een nieuwe tabel met een INSERT INTO tabel (col1, col2, ...) SELECT ...
Als je ID's recursief zijn maak je gebruik van een recursieve common table expression.

Hier een foto van een deel van de relaties


Je ziet dat de tabellen beginnen met trn_ een andere groep begint met tr2_

De id's van alle tabellen beginnen logisch met 1.

Dus in trn_groep, totaal 3 records, heb je id 1 maar ook in tr2_groep. Dus als ik deze samenvoeg wordt het id in trn_groep 4 welkde slechts 1 records heeft.
gevolg alles met verwijzing groepid uit tr2_ moet aangepast worden van 1 naar 4 tijdens de importatie

Dit loopt verder uit met de toernooien ±50 en 3 records welke weer moeten overgenomen worden in de partijen, 3570+96 records, en de spelers, 469+26 records.

In totaal 13 tabellen met hun records

Er zijn geen relaties gelegd via:

ALTER TABLE trn_toernooien ADD CONSTRAINT FK_2 FOREIGN KEY (groep) REFERENCES trn_groepen (id);

Die worden slechts in de query's gevraagd.

Jan

Aan de screenshot te zien gebruik je MySQL/MariaDB ?
Waarom zijn er geen FK-relaties gelegd, heeft dat te maken met de storage engine ?

Als ik in jouw schoenen stond, en de storage engine mag InnoDB (standaard) zijn, dan zou ik

1.) FK-relaties aanleggen. Dan worden bij een update in meerdere tabellen tegelijk de ID's bijgewerkt, bijvoorbeeld zo:

ALTER TABLE groep ADD CONSTRAINT trn_toernooien_groep_id_fkey 
  REFERENCES trn_toernooien (id) ON DELETE RESTRICT ON UPDATE CASCADE;

Zie: https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html

Als je dan het ID zou veranderen, in trn_groepen, dan verandert de ID mee in de tabel trn_toernooien:

UPDATE trn_groepen SET id = 1 WHERE ... LIMIT 1;

De kolom id van de tabel trn_groepen zou idealiter een PRIMARY KEY zijn.

2.) De id's van de soortgelijke tabellen op elkaar afstemmen, bijvoorbeeld:

UPDATE trn_groepen SET id = id + (SELECT MAX(id) FROM tr2_groepen);

3.) Nieuwe tabellen aanmaken met dezelfde structuur en relaties als de soortgelijke tabellen

4.) De nieuwe tabellen vullen

INSERT INTO groepen
SELECT * FROM trn_groepen
UNION ALL SELECT * FROM tr2_groepen
UNION ALL SELECT * FROM ...

5.) De oude tabellen verwijderen

Als de tabellen die je wilt samenvoegen dezelfde structuur hebben, kun je het waarschijnlijk het gemakkelijkst en veiligst oplossen met een numerieke prefix.

Stel, de grootste tabel bevat minder dan 20.000 records. Je kunt dan 2000 toevoegen voor de ID's in de kleinere tabel:

- 1 wordt 20001
- 2 wordt 20002
- 3 wordt 20003
enzovoort

Ik zou dat zelf even doen met zoeken en vervangen in een export van de SQL-data, zodat je ziet wat je doet, maar je kunt de operatie ook uitvoeren met de stringfunctie CONCAT() in SQL.

Beiden bedankt. ik hoopte op minder werk :) maar dacht al dat dit de beste oplossing zou zijn.
resumeren
1. FK relaties aanmaken
2. update table ... set id=id + max_van_andere_tabel voor elke tabel
3. tabellen samenvoegen

PS ik maak geen fk relaties omdat mijn restore-script hier problemen mee heeft. Eerst worden de tabellen verwijderd dan opnieuw aangemaakt en dan opnieuw gevuld.
[code]
DROP TABLE IF EXISTS dvd_films;

CREATE TABLE dvd_films (
id int(11) NOT NULL AUTO_INCREMENT,
titel varchar(50) NOT NULL,
subtitels text NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=242 DEFAULT CHARSET=utf8;

INSERT INTO dvd_films VALUES("1", "3 FAMILY MOVIES", "");
# en dan nog 241 records natuurlijk
[code]

Bij het verwijderen lukt het niet omdat de andere, benodigde, tabel nog bestaat.

Jan

in zo'n geval is het verstandiger om de check tijdelijk uit te zetten in plaats van helemaal niet te gebruiken:

SET FOREIGN_KEY_CHECKS=0;
SET FOREIGN_KEY_CHECKS=1;

zowel bij verwijderen als inserten van records in willekeurige volgorde.
Al lijkt me een restore iets wat je normaal op een hele database doet en niet per losse tabel.

Die kende ik nog niet. Bedankt!

Omdat one.com geen backups kent moet ik me behelpen met een script.
Script loopt door de db een maakt een restorescript aan
inhoud restorescript(sql bestand)
1 verwijder tabel
2 maak de tabel opnieuw met de indexen
3 voeg records toe
4 ga naar volgende tabel en keer terug naar punt 1 indien er nog tabellen zijn.

Jan

Ik ben dus vollop bezig met de CONSTRAINT's aan te maken. Ik heb er al 48. Ik heb er echter ook 1tje die niet verplicht een link heeft.
Het gaat zoals meestal weer over schaken:) maar soms heeft iemand geen tegenstander. In dat geval wordt naar gelang de reden -2,-1 of 0 gebruikt. Deze tegenstanders bestaan niet. Ze worden hardcoded opgevangen als vrijwillig niet spelen, geen tegenstander en afwezig.

Behalve ze in elk toernooi aanmaken is er eventueel een oplossing hiervoor?

ALTER TABLE ldr_partijen ADD CONSTRAINT Fk_143 FOREIGN KEY (wit) REFERENCES ldr_spelers (id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE ldr_partijen ADD CONSTRAINT Fk_144 FOREIGN KEY (zwart) REFERENCES ldr_spelers (id) ON DELETE CASCADE ON UPDATE CASCADE;

De 1° lukt dus maar de 2° niet omdat zwart -2 tot 0 kan zijn welke niet bestaan in de database.

Jan

Als er geen record bestaat waar naar verwezen kan worden, is het gebruikelijk om in de kolom met de FK-contraint een NULL te zetten. Dat is geen probleem voor de FK-constraint, die controleert alleen maar of de waarde waar naar verwezen wordt bestaat, als er überhaupt een waarde is opgegeven.
Aannemende dat 0 de niet-bestaande waarde is, kan je kiezen om 0 toe te voegen in de andere tabel, of 0 te vervangen voor NULL.

kun je geen mysqldump gebruiken?

Dat is een uitgedachte output om een database mee te restoren. (of alleen de data, of alleen de tabellen/view, evt in- of exclusief procedures etc).

Dat werkt altijd beter dan een eigen script, waarbij je zoals hier tegen FK restricties aanloopt. Die FK-check regel zul je dan ook vaak in de output van mysqldump zien staan.