Kolom importeren in bestaande tabel
- Ariën - op 07/02/2022 15:26:30:
Wat doe je precies? Heb je relevante code?
Toevoeging op 07/02/2022 16:45:36:
Dit is alleen maar een testje:
Quote:
ALTER TABLE `jos_content` ADD `test` int(10) UNSIGNED NOT NULL DEFAULT 0;
INSERT INTO `jos_content`(`test`) VALUES
(47),
(49);
INSERT INTO `jos_content`(`test`) VALUES
(47),
(49);
Code (php)
1
UPDATE tablename SET test=47 WHERE id=x
Hierbij is x het nummer van de rij die aangepast moet worden...
Ik ben geruime tijd bezig geweest om een Joomla website versie 1.5 te migreren naar versie 3.9. De site bevat veel artikelen (meer dan 1000). De oude site staat nog steeds live. Als laatste stap van de migratie wil ik de actuele hits (aantal bezoeken) overzetten naar de gemigreerde site.
Ik heb (voorlopig als test) de kolom “hits” van de live site geëxporteerd als .sql bestand.
1. Ik heb de kolom “hits” uit de nieuwe site geëxporteerd als .sql bestand. Ik heb daar wat wijzigingen in aangebracht en geprobeerd de kolom (onder een andere naam) weer te importeren. Dat lukt me nog steeds niet.
2. Ik heb de kolom “hits” uit de nieuwe site geëxporteerd als .csv excel bestand. Hierbij worden maar max 349 rijen geëxporteerd!!
Ik hoop dat je/jullie mij kunnen helpen met deze laatste stap zodat ik mijn nieuwe site live kan zetten.
En hoe zien beide structuren eruit?
https://dba.stackexchange.com/questions/11811/mysql-csv-update-not-insert-into-existing-table
Met deze zoekopdracht vind je meer oplossingen.
mariadb update csv into table
Toevoeging op 09/02/2022 13:20:46:
Voorbeeld hoe je een csv bestand inleest en update in een bestaande tabel.
Bewaar dit als demodemotekst.txt op een plek waarvan het verderop (path/to) wordt ingelezen.
Code (php)
1
2
3
4
2
3
4
1;Demo text - om te laden - in een stored procedure
2;Meer text - met een pipe
3;Wanneer er - meer - ruimte is - om te gaan vissen
4;Dan is - het vroeg - genoeg om - maatregelen te nemen
2;Meer text - met een pipe
3;Wanneer er - meer - ruimte is - om te gaan vissen
4;Dan is - het vroeg - genoeg om - maatregelen te nemen
Voer dan deze sql code uit.
Pas bij LOAD DATA INFILE de padnaam aan.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
DROP TABLE IF EXISTS `demo_data`;
CREATE TABLE `demo_data` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`demo_tekst` char(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `demo_data` (`id`, `demo_tekst`) VALUES
(1, 'Demo text;om te laden;in een stored procedure'),
(2, 'Meer text;met een pipe'),
(3, 'Wanneer er;meer;ruimte is;om te gaan vissen'),
(4, 'Dan is;het vroeg;genoeg om;maatregelen te nemen');
SELECT * FROM demo_data;
CREATE TEMPORARY TABLE temp_demo_table LIKE demo_data;
LOAD DATA INFILE '/path/to/demodemotekst.txt'
INTO TABLE temp_demo_table
FIELDS TERMINATED BY ';' (id, demo_tekst);
UPDATE demo_data
INNER JOIN temp_demo_table
ON temp_demo_table.id = demo_data.id
SET demo_data.demo_tekst = temp_demo_table.demo_tekst;
SELECT * FROM demo_data;
CREATE TABLE `demo_data` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`demo_tekst` char(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `demo_data` (`id`, `demo_tekst`) VALUES
(1, 'Demo text;om te laden;in een stored procedure'),
(2, 'Meer text;met een pipe'),
(3, 'Wanneer er;meer;ruimte is;om te gaan vissen'),
(4, 'Dan is;het vroeg;genoeg om;maatregelen te nemen');
SELECT * FROM demo_data;
CREATE TEMPORARY TABLE temp_demo_table LIKE demo_data;
LOAD DATA INFILE '/path/to/demodemotekst.txt'
INTO TABLE temp_demo_table
FIELDS TERMINATED BY ';' (id, demo_tekst);
UPDATE demo_data
INNER JOIN temp_demo_table
ON temp_demo_table.id = demo_data.id
SET demo_data.demo_tekst = temp_demo_table.demo_tekst;
SELECT * FROM demo_data;
Code (php)
1
2
3
2
3
UPDATE nieuweTabel t1
JOIN OudeTabel t2 ON t1.id = t2.id
SET t1.hits = t2.hits
JOIN OudeTabel t2 ON t1.id = t2.id
SET t1.hits = t2.hits
Kloon demo_data table met opgegeven velden en kopieer alle data.
Code (php)
1
CREATE table demo_data_temp SELECT id, demo_tekst FROM demo_data;
OF DEZE
Kloon demo_data table met opgegeven velden en kopieer de data met selectie.
Code (php)
1
CREATE table demo_data_temp SELECT id, demo_tekst FROM demo_data WHERE id = 2;
Bekijk de data in demo_data_temp.
Code (php)
1
SELECT * FROM demo_data_temp;
Bewerk vervolgens de data in demo_data_temp.
Update demo_data table.
Code (php)
1
2
3
4
2
3
4
UPDATE demo_data d1
INNER JOIN demo_data_temp d2
ON d1.id = d2.id
SET d1.demo_tekst = d2.demo_tekst;
INNER JOIN demo_data_temp d2
ON d1.id = d2.id
SET d1.demo_tekst = d2.demo_tekst;
Bekijk de bijgewerkte data.
Code (php)
1
SELECT * FROM demo_data;
Gewijzigd op 09/02/2022 20:12:34 door Adoptive Solution
- met phpMyAdmin een CSV-export maken van de tabel met het aantal hits uit de Joomla 1.5 database
- die weer inlezen met phpMyAdmin in de database van Joomla 3.9 onder een andere naam, zeg `oudetabel`
Vervolgens kan je met een enkele UPDATE-query in de Joomla 3.9 database het aantal hits overzetten.
Iets als:
Code (php)
1
2
3
2
3
UPDATE `tabel` SET `hits` = `oudetabel`.`hits`
FROM `oudetabel`
WHERE `oudetabel`.`id` = `tabel`.`id`;
FROM `oudetabel`
WHERE `oudetabel`.`id` = `tabel`.`id`;
Wanneer alles is overzet kan je de oude tabel verwijderen (DROP TABLE `oudetabel`;)
Frank Nietbelangrijk op 09/02/2022 16:32:50:
Ik zou zeggen Of schrijf een php script die de hits uit de oude tabel inleest en in de nieuwe tabel wegschrijft of doe het met een tool zoals phpMyAdmin door beiden tabellen (desnoods met 1 met een aangepaste naam) in je database te schrijven waarna je de waarden van de oude tabel kopieert naar de nieuwe tabel:
Code (php)
1
2
3
2
3
UPDATE nieuweTabel t1
JOIN OudeTabel t2 ON t1.id = t2.id
SET t1.hits = t2.hits
JOIN OudeTabel t2 ON t1.id = t2.id
SET t1.hits = t2.hits
Sorry voor mijn (zeer) late reactie.
Een klein testje leert dat deze code een eenvoudige manier is om de hits over te zetten. Ik loop echter tegen het volgende probleem aan: Bij het migreren van mijn artikelen zijn de ID's niet gelijk gebleven maar opgehoogd. Ik probeer nu de ID's te resetten met de volgende code:
Code (php)
1
2
3
2
3
SET @autoid := 0;
UPDATE `jos_content` SET id = @autoid := (@autoid+1);
ALTER TABLE `jos_content` auto_increment = 1;
UPDATE `jos_content` SET id = @autoid := (@autoid+1);
ALTER TABLE `jos_content` auto_increment = 1;
Bij de nieuwe tabel gaat dit goed echter bij de oude tabel krijg ik de melding:
Quote:
SQL-query: UPDATE `jos_content` SET id = @autoid := (@autoid+1);
MySQL meldt: #1062 - Dubbele ingang '3' voor zoeksleutel 'PRIMARY'
MySQL meldt: #1062 - Dubbele ingang '3' voor zoeksleutel 'PRIMARY'
Heb je enig idee hoe dit op te lossen is?
Code (php)
1
UPDATE `jos_content` SET id = -id;
Wat je doet is de records aflopen zoals mysql ze vind. De 1° krijgt het nieuwe id 1, Het 2° 2 en zo verder. echter bij 3 is er al een ander record met id 3 en hier loopt het dus op vast.
Met dergelijke code moet je wel opletten dat je je joins niet gaat verminken.
Dit doe ik ook met mijn "oude" jos_content in de hoop dat mijn ID's gelijk worden. Helaas komen er hier en daar verschuivingen voor. Ik ben vrij lang bezig geweest om de volgorde van artikelen en ID's gelijk te krijgen en daarna op volgorde te resetten. Dit lukt steeds niet helemaal.
Mijn vraag is: Is het mogelijk om ID's te resetten in datumvolgorde?
ID's zijn uit praktisch oogpunt met auto-increment oplopend uitgegeven.
Maar je had ze ook uit kunnen geven als
10, daarna 9, 8 tot aan 1
daarna
20, en aflopend tot 11.
Hoofdzaak is dat ze uniek zijn. En je gaat ze zeker niet aanpassen met als doel om te sorteren.
Kijk naar je BSN nummer: dat is een uniek nummer van 9 cijfers.
Maar ze worden uitgegeven in een compleet willekeurige volgorde.
Zelfs voor een tweeling kan dat nummer voor de een met 1234 beginnen en de ander met 9876
Maakt ook niet uit, want het nummer is niet bedoeld om leeftijden of geslacht danwel geboorteplaats uit af te kunnen leiden.
Net zo met je ID: dat nummer kan af en toe eens in een url opduiken, maar boeit verder niet.
Alleen als je door een foute query je id's hebt veranderd, kan het nuttig zijn om te proberen die terug te zetten. Maar dan terug naar wat het was (of je pakt een backup).
Maar als je vorig jaar een boek in je webshop verkocht met ID 1234 en omdat je na een besluit om boeken niet langer op alfabetische volgorde van titel, maar van schrijver te sorteren dit ID aanpast naar 9887
Dan ziet de klant in zijn bestel geschiedenis ineens dat hij een heel ander boek besteld zou hebben??
Sowieso:
Als je goed met FK restricties werkt, zou het al tot problemen moeten leiden als je ID ineens verdwijnt.
(of je hele database zou in alle tabellen het betreffende ID moeten updaten)
Bottom-line: van je ID kolom blijf je gewoon af: die gebruik je niet om aan klant of sitebheerder te tonen.
Toevoeging op 24/03/2022 09:40:03:
Oh ja:
en ORDER BY idkolom
zou je niet moeten gebruiken.
Je sorteert op naam, datum (invoerdatum, geboortedatum, verkoopdatum), of op prijs, kleur of wat voor eigenschap dan ook.
ID is alleen voor
WHERE idkolom = 1234;
09/02/2022 16:32:50 geeft prima werkt is het van belang dat de ID's per artikel gelijk zijn (hoeft dus niet per se op volgorde). Het enige wat per artikel gelijk is, is de datum - tijd vandaar mijn vraag: Is het mogelijk om ID's te resetten in datumvolgorde?
Zorg voor een nieuwe lege tabel met alle kolommen er in maar zonder records.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
<?php
$id = 1;
// lees alle records in op volgorde van datum
$stmt = $pdo->query("SELECT * FROM old_table ORDER BY datum ASC"); // of DESC ?
// in deze volgorde vul een andere nieuwe nog lege tabel met dezelfde data
while ($row = $stmt->fetch()) {
$sql = "INSERT INTO new_table (id, name, datum) VALUES (?,?,?)";
$stmt2 = $pdo->prepare($sql);
$stmt2->execute([$id++, $row['name'], $row['datum']]);
}
?>
$id = 1;
// lees alle records in op volgorde van datum
$stmt = $pdo->query("SELECT * FROM old_table ORDER BY datum ASC"); // of DESC ?
// in deze volgorde vul een andere nieuwe nog lege tabel met dezelfde data
while ($row = $stmt->fetch()) {
$sql = "INSERT INTO new_table (id, name, datum) VALUES (?,?,?)";
$stmt2 = $pdo->prepare($sql);
$stmt2->execute([$id++, $row['name'], $row['datum']]);
}
?>
Gewijzigd op 24/03/2022 23:47:39 door Frank Nietbelangrijk
Code (php)
1
2
2
INSERT INTO new_table (id, name, datum)
SELECT null, name, datum FROM old_table ORDER BY datum ASC
SELECT null, name, datum FROM old_table ORDER BY datum ASC
Maar dat gaat er wel vanuit dat de reeks die je wilt matchen
a) geen dubbele datum-tijd combi bevat
b) geen gaten in de reeks heeft.
Dan zou ik eerder gaan voor
Code (php)
1
2
3
4
5
2
3
4
5
INSERT INTO new_table (id, naam, datumtijd, overige, kolommen)
SELECT oude_tabel.id,
invoer_tabel.naam, invoer_tabel.datumtijd, invoer_tabel.overige, invoer_tabel.kolommen
FROM invoer_tabel
JOIN oude_tabel ON oude_tabel.datumtijd = invoer_tabel.datumtijd
SELECT oude_tabel.id,
invoer_tabel.naam, invoer_tabel.datumtijd, invoer_tabel.overige, invoer_tabel.kolommen
FROM invoer_tabel
JOIN oude_tabel ON oude_tabel.datumtijd = invoer_tabel.datumtijd
Daarmee zou je een nieuwe tabel op kunnen bouwen die uiteindelijk dan de aangepaste versie is van je oude_tabel. En dan is uiteindelijk alleen een rename nodig.
Gebruik hiervoor de ROW_NUMBER() functie van MySQL om een nieuwe (of bestaande) kolom met ID's te vullen. Voorbeeld:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE `mijntabel` AS
SELECT CURRENT_DATE AS `datum`
UNION SELECT DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)
UNION SELECT DATE_SUB(CURRENT_DATE, INTERVAL 2 DAY);
ALTER TABLE `mijntabel` ADD COLUMN `id` int;
CREATE TABLE `mijntabel_id` AS
SELECT ROW_NUMBER() OVER () AS `id`, `datum`
FROM `mijntabel`;
UPDATE `mijntabel`
INNER JOIN `mijntabel_id` ON `mijntabel`.`datum` = `mijntabel_id`.`datum`
SET `mijntabel`.`id` = `mijntabel_id`.`id`;
DROP TABLE `mijntabel_id`;
SELECT CURRENT_DATE AS `datum`
UNION SELECT DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)
UNION SELECT DATE_SUB(CURRENT_DATE, INTERVAL 2 DAY);
ALTER TABLE `mijntabel` ADD COLUMN `id` int;
CREATE TABLE `mijntabel_id` AS
SELECT ROW_NUMBER() OVER () AS `id`, `datum`
FROM `mijntabel`;
UPDATE `mijntabel`
INNER JOIN `mijntabel_id` ON `mijntabel`.`datum` = `mijntabel_id`.`datum`
SET `mijntabel`.`id` = `mijntabel_id`.`id`;
DROP TABLE `mijntabel_id`;
Achteraf zou je nog met de MAX() -functie de sequence goed kunnen zetten, bijvoorbeeld zo:
Code (php)
1
2
3
4
2
3
4
ALTER TABLE `mijntabel` ADD PRIMARY KEY (`id`);
ALTER TABLE `mijntabel` MODIFY `id` int NOT NULL AUTO_INCREMENT;
SELECT MAX(`id`) FROM `mijntabel`; -- 3
ALTER TABLE `mijntabel` AUTO_INCREMENT = 4;
ALTER TABLE `mijntabel` MODIFY `id` int NOT NULL AUTO_INCREMENT;
SELECT MAX(`id`) FROM `mijntabel`; -- 3
ALTER TABLE `mijntabel` AUTO_INCREMENT = 4;
Als PHP beginner loop je tegen veel zaken aan. Ik heb onderstaande code opgenomen in een ,php bestand in de map van de website. Echter ik krijg een foutmelding. Ik heb ‘naam’ en ‘datum’ hernoemd naar ‘title’ en ‘created’. Ook heb ik de databasenaam toegevoegd. Ik doe vast iets verkeerd !
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
<?php
$id = 1;
// lees alle records in op volgorde van datum
$stmt = $pdo->query("SELECT * FROM 'deb27035_joomla'.'kjmm_content-oud' ORDER BY created ASC"); // of DESC ?
// in deze volgorde vul een andere nieuwe nog lege tabel met dezelfde data
while ($row = $stmt->fetch()) {
$sql = "INSERT INTO 'deb27035_joomla'.'kjmm_content-nieuw' (id, title, created) VALUES (?,?,?)";
$stmt2 = $pdo->prepare($sql);
$stmt2->execute([$id++, $row['title'], $row['created']]);
}
?>
$id = 1;
// lees alle records in op volgorde van datum
$stmt = $pdo->query("SELECT * FROM 'deb27035_joomla'.'kjmm_content-oud' ORDER BY created ASC"); // of DESC ?
// in deze volgorde vul een andere nieuwe nog lege tabel met dezelfde data
while ($row = $stmt->fetch()) {
$sql = "INSERT INTO 'deb27035_joomla'.'kjmm_content-nieuw' (id, title, created) VALUES (?,?,?)";
$stmt2 = $pdo->prepare($sql);
$stmt2->execute([$id++, $row['title'], $row['created']]);
}
?>
2. Ivo P
Met de volgende code kom ik een heel eind, maar net niet helemaal:
Code (php)
1
2
3
4
5
2
3
4
5
INSERT INTO `kjmm_content-nwtmp` (id, title, created)
SELECT null, title, created FROM `kjmm_content-nieuw` ORDER BY created ASC
INSERT INTO `kjmm_content-oudtmp` (id, title, created, hits)
SELECT null, title, created, hits FROM `kjmm_content-oud` ORDER BY created ASC
SELECT null, title, created FROM `kjmm_content-nieuw` ORDER BY created ASC
INSERT INTO `kjmm_content-oudtmp` (id, title, created, hits)
SELECT null, title, created, hits FROM `kjmm_content-oud` ORDER BY created ASC
en vervolgens de hits overzetten:
Code (php)
1
2
3
2
3
UPDATE `kjmm_content-nwtmp` t1
JOIN `kjmm_content-oudtmp` t2 ON t1.id = t2.id
SET t1.hits = t2.hits
JOIN `kjmm_content-oudtmp` t2 ON t1.id = t2.id
SET t1.hits = t2.hits
Ik heb nu echter alleen de kolommen id, title, created en hits. Ik ben er nog niet achter gekomen hoe ik al de andere kolommen kan overbrengen naar de nieuwe tabel. Of moet ik alle kolommen apart gaan benoemen zoals je aangeeft in:
Code (php)
1
INSERT INTO new_table (id, naam, datumtijd, overige, kolommen)
3. Ad Fundum
Bij de uitvoering van jouw code bij de SQL-tab en de benaming aangepast voor mijn tabellen krijg ik bij:
Code (php)
1
SELECT ROW_NUMBER() OVER () AS `id`, `datum`
de volgende foutmeldingen:
Een alias was al eerder gevonden. (nabij ‘id’)
Een alias werd verwacht. (nabij )
Onverwacht token. (nabij ‘id’)
Onverwacht token. (nabij , )
Onverwacht token. (nabij ‘datum’)
Het resultaat is:
Datum id
2022-03-30 3
2022-03-29 1
2022-03-28 2
Gewijzigd op 30/03/2022 16:33:17 door Dick VanBruggen
Wat ik liet zien was maar een voorbeeld snippet.
Voordat je met dat voorbeeld gaat werken zul je eerst verbinding moeten maken met je database:
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
<?php
try {
$pdo = new PDO('mysql:host=localhost;dbname=databaseNaam', 'username', 'password', array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
));
}
// wijzig databaseNaam, username en password naar jouw mysql instellingen.
?>
try {
$pdo = new PDO('mysql:host=localhost;dbname=databaseNaam', 'username', 'password', array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
));
}
// wijzig databaseNaam, username en password naar jouw mysql instellingen.
?>
Daarnaast is de algemene regel dat je er voor zorgt dat je script fouten laat zien. Die staan namelijk op de hosting standaard uitgeschakeld. Op je dashboard van je provider kun je deze waarschijnlijk aanzetten maar je kunt ook de foutafhandeling aanzetten door het volgende boven in je script te plakken:
In totaal krijg je dan:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<?php
ini_set('display_errors', 1);
error_reporting(E_ALL);
try {
$pdo = new PDO('mysql:host=localhost;dbname=databaseNaam', 'username', 'password', array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
));
}
$id = 1;
// lees alle records in op volgorde van datum
$stmt = $pdo->query("SELECT * FROM 'deb27035_joomla'.'kjmm_content-oud' ORDER BY created ASC"); // of DESC ?
// in deze volgorde vul een andere nieuwe nog lege tabel met dezelfde data
while ($row = $stmt->fetch()) {
$sql = "INSERT INTO 'deb27035_joomla'.'kjmm_content-nieuw' (id, title, created) VALUES (?,?,?)";
$stmt2 = $pdo->prepare($sql);
$stmt2->execute([$id++, $row['title'], $row['created']]);
}
?>
ini_set('display_errors', 1);
error_reporting(E_ALL);
try {
$pdo = new PDO('mysql:host=localhost;dbname=databaseNaam', 'username', 'password', array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
));
}
$id = 1;
// lees alle records in op volgorde van datum
$stmt = $pdo->query("SELECT * FROM 'deb27035_joomla'.'kjmm_content-oud' ORDER BY created ASC"); // of DESC ?
// in deze volgorde vul een andere nieuwe nog lege tabel met dezelfde data
while ($row = $stmt->fetch()) {
$sql = "INSERT INTO 'deb27035_joomla'.'kjmm_content-nieuw' (id, title, created) VALUES (?,?,?)";
$stmt2 = $pdo->prepare($sql);
$stmt2->execute([$id++, $row['title'], $row['created']]);
}
?>
Toevoeging op 30/03/2022 18:46:40:
Omdat je zegt dat je een PHP bestand hebt aangemaakt in de map van je website even het volgende:
GEWIS jezelf er van dat je een goede BACKUP hebt van zowel je bestanden en de data uit de database alvorens je gaat lopen "knoeien" op je productie server!
Wanneer je het helemaal goed wilt doen dan richt je een testomgeving in waarin een exacte kopie staat van je website en dan ga je daarmee aan het sleutelen!