[MySQL] Database migratie - primary key koppeling

[Vinze]

Ik ben bezig met het migreren van een database alleen hier loop ik tegen een aantal problemen aan waar ik maar niet uit kom. Ik heb namelijk een tabel klanten, en een tabel bestellingen. De items in de tabel bestellingen zit gekoppeld aan de tabel klanten doormiddel van het Klant_ID. Nu wil ik dit graag vervangen door het ID uit de tabel klanten, deze is ingesteld op auto_increment en is ook de primary key. Helaas heb ik geen flauw idee hoe ik zo'n query op kan bouwen, daarom hoop ik dat iemand me hier bij kan helpen.

Even een voorbeeld tabel van de huidige situatie:

Tabel klanten

ID	Klant_ID	Naam
0	joost0021	Joost de Boer
1	meike0022	Meike de Groot
8	david0065	David de Jong

Tabel bestellingen

ID	Item	Klant_ID
2	iPhone	meike0022 (moet dus 1 worden)
3	Galaxy S	joost0021 (moet dus 0 worden)
6	HTC Desire	david0065 (moet dus 8 worden)

Iemand die me uit de brand kan helpen?

[ajakkes]

Een klanten_ID kolom toevoegen aan 'bestellingen'

$x = Select 'klant_ID' from 'bestellingen' where 'klanten_ID' is '' limit 1

$y = Select 'ID' from 'klanten' where 'Klant_ID' is $x

Update 'Klanten_ID' to $y from 'bestellingen' where 'Klant_ID' is $x

Klant_ID verwijderen van 'bestellingen'


Code klopt van geen kant maar de intentie is zal duidelijk zijn.

[Manuel]

Nu ga ik ervan uit dat je PHP gebruikt en dat je snapt hoe een array werkt samen met de basis van query's. Zo pak ik het altijd aan, geen gedoe met geneste query's al verwacht ik dat sommigen hier er een andere visie over hebben.

Ik hoop dat je in ieder geval snapt wat dit onderstaande stukje doet en dus niet klakkeloos kopieert.

PHP:

$KlantenIds = $db->query("SELECT `id`, `klant_id` FROM `klanten`");

foreach($KlantenIds->fetch(PDO::FETCH_ASSOC) as $key => $value) {
    // Al kan ik je aanraden een test tabel te maken en het daar eerst op los te gooien. Mocht het fout gaan kun je het tabel zo opnieuw aanmaken.
    $BestelSth = $db->prepare("UPDATE `bestellingen` SET `klant_id` = ? WHERE `klant_id` = ?");
    $BestelSth->execute(array(
        $value["id"],
        $value["klant_id"]
    ));
}

( De vraagtekens worden automatisch ingevuld, zie de array voor de waardes. )

[Thralas]

Manuel schreef op donderdag 04 augustus 2011 @ 12:01:
Nu ga ik ervan uit dat je PHP gebruikt en dat je snapt hoe een array werkt samen met de basis van query's. Zo pak ik het altijd aan, geen gedoe met geneste query's al verwacht ik dat sommigen hier er een andere visie over hebben.

De subquery-optie lijkt wel de meest triviale oplossing.

TS, bedenk eens:
- Een update-query om het Klant_ID-field voor elke rij te updaten
- Voor de waarde van het Klant_ID-field, een subquery welke het ID van een klant ophaalt, gegeven zijn/haar Klant_ID.

Als 't goed is heb je 'm dan helemaal. In SQL. Zonder gedoe met arbitraire scriptingtalen als PHP

edit:
Met een join kan natuurlijk ook, is wat netter/sneller, zie hieronder

[ Voor 5% gewijzigd door Thralas op 04-08-2011 13:09 ]

[HarryKiri]

Volgens mij lost onderstaande procedure het probleem op waar je mee zit. Overigens is dit basiskennis, wellicht is het een goed idee om hier eerst wat aan te schaven voordat je dergelijke zaken gaat ondernemen?

(Gebruik phpMAdmin of een andere applicatie waarmee je kunt connecten met de database en SQL kunt uitvoeren.)

1. Voeg een nieuwe kolom toe aan de tabel bestellingen waarin het ID van de tabel Klanten opgeslagen kan worden.

2. Voer onderstaande query uit:
UPDATE bestellingen b INNER JOIN klanten k SET b.[naam nieuwe kolom] = k.ID WHERE b.Klant_ID = b.Klant_ID;

3. Verwijder kolom Klant_ID uit tabel bestellingen.

Succes!

[SoulWar1]

Het lijkt me dat dit ook op te lossen is met een enkele update query.

UPDATE bestellingen b SET b.Klant_ID = (SELECT k.ID FROM klanten k WHERE k.Klant_ID=b.Klant_ID)

[GlowMouse]

SoulWar1 schreef op donderdag 04 augustus 2011 @ 21:50:
Het lijkt me dat dit ook op te lossen is met een enkele update query.

UPDATE bestellingen b SET b.Klant_ID = (SELECT k.ID FROM klanten k WHERE k.Klant_ID=b.Klant_ID)

Dit is inderdaad beter dan zo'n script. Maar aangezien het datatype van Klant_ID ook veranderd moet worden, zou ik eerst een nieuwe kolom maken met het juiste datatype en daar het id in opslaan. Als het misgaat dan hoef je ook niet direct je backup terug te zetten. Pas als alles goed staat, kan de oude kolom Klant_ID weg.

Wat heeft dit met een migratie te maken?

[Voutloos]

Nofi maar als je een database moet migreren wordt je wel verondersteld te weten hoe je een waarde selecteert uit kolom A met als voorwaarde iets in kolom B en hoe je de update syntax in de docs moet lezen?

[ Voor 27% gewijzigd door Voutloos op 04-08-2011 22:15 ]

[Vinze]

Ja die basis kennis in MySQL heb ik wel, alleen dit had ik nog nooit gedaan en ik had geen idee waar ik moest beginnen. Kon ook niet de goede zoekwoorden vinden om tot nuttige resultaten te komen op Google & GoT. En of dit een migratie is? Tja ik kon ook geen betere topic titel bedenken.

In ieder geval bedankt voor de hulp tot nu toe, morgen ga ik er even mee stoeien en kijken of ik het voor elkaar krijg. Mocht het niet goed gaan is dit ook geen ramp, een backup van de database is snel terug gezet. (ik werk uiteraard niet live)