[MySQL] Twee moeilijke queries (ALTER TABLE en DELETE)

[kramer65]

Hallo,

Ik heb een databank waarop ik twee queries wil runnen. Aangezien het echter een ALTER TABLE en een DELETE query zijn, wilde ik hier eerst even verifieren of ze correct zijn voordat ik de boel compleet in de war schop.

Ik heb dus een databank met een aantal tables heb die allemaal identiek zijn (muv de naam en de inhoud). Nu wil ik twee velden ('Open' en 'Slot') MODIFY'en in alle tables tegelijk. Ik dacht aan iets als dit:

SQL:

ALTER TABLE %
MODIFY `Open` decimal(17,2)
AND `Slot` decimal(17,2)

Zou dit kloppen?


Ook wil ik vervolgens een delete doen van alle records in alle tables waarin in het Open-veld een 0 staat en het Slot-veld hetzelfde is als het Slot-veld van de dag ervoor. Dit bedacht ik las volgt te doen:

SQL:

DELETE FROM % n1
WHERE `Open` = 0
AND `Slot` = (SELECT MAX(n2.Slot)
  FROM % n2
  WHERE n2.Datum < n1.Datum
        )

Weet iemand of hier iets van klopt? (of is dit gewoon klinklare onzin.. )

Alle tips zijn welkom!

[Voutloos]

Los van wat je uberhaupt doet: Als je zo bang bent probeer je het toch op een kopietje?

[RobIII]

kramer65 schreef op vrijdag 13 mei 2011 @ 17:33:
wilde ik hier eerst even verifieren of ze correct zijn voordat ik de boel compleet in de war schop.

Wat is er mis met een test-DB opzetten en 't daar eens op proberen?

kramer65 schreef op vrijdag 13 mei 2011 @ 17:33:

SQL:

ALTER TABLE %
MODIFY `Open` decimal(17,2)
AND `Slot` decimal(17,2)

Zou dit kloppen?

Ik mag hopen van niet

kramer65 schreef op vrijdag 13 mei 2011 @ 17:33:

SQL:

DELETE FROM % n1
WHERE `Open` = 0
AND `Slot` = (SELECT MAX(n2.Slot)
  FROM % n2
  WHERE n2.Datum < n1.Datum
        )

Weet iemand of hier iets van klopt? (of is dit gewoon klinklare onzin.. )

Klinkklare onzin.

kramer65 schreef op vrijdag 13 mei 2011 @ 17:33:
Alle tips zijn welkom!

Tip: Begin eens met de basis voordat je in 't diepe springt. En zet een test-server / test-DB op waarop je je kunt uitleven. En bij twijfel pak je gewoon de manual erbij:

Delete syntax
Alter table syntax

kramer65 schreef op vrijdag 13 mei 2011 @ 17:33:
Ik heb dus een databank met een aantal tables heb die allemaal identiek zijn (muv de naam en de inhoud).

Waarom Heb je aan normalisatie gedaan? Zo nee: nog een basiscursusje doen

[ Voor 18% gewijzigd door RobIII op 13-05-2011 17:44 ]

[kramer65]

Ok, een testservertje is misschien wel een goed idee dan. Maar de vraag is meer; hoe kan je een query in één keer op alle tables in een DB doen? Misschien daar een tipje over? Ik heb namelijk wel zitten zoeken en bij een SELECT geef je normaal gewoon alle tables aan. Nou kan ik dat wel doen, maar dan kom ik in de rommel met de subqueries waarvan ik dan niet weet hoe je aan kan geven dat het een subquery is die op dezelfde table moet runnen als waarop de hoofdquery op dat moment runt.

Misschien nog een stapje terug. Als ik mijn eerste query nou op één table wil doen, hoe doe ik die MODIFY dan op twee velden. Ik heb dit geprobeerd:

SQL:

ALTER TABLE aex
MODIFY `Open` decimal(17,2)
AND `Slot` decimal(17,2)

Maar dat geeft een foutmelding. Het AND is duidelijk niet goed. Iemand een tipje misschien..


--EDIT--
@Roblll
De tables zijn allemaal hetzelfde omdat ze allemaal een beursindex bevatten over de afgelopen jaren. Zo heb ik de table aex waarin alle Open en Slot koersen staan over de laatste decennia. Hetzelfde heb ik voor zo'n 30 andere beursindexen. Zodoende dat ze kwa opzet allemaal hetzelfde zijn..

[ Voor 15% gewijzigd door kramer65 op 13-05-2011 17:51 ]

[RobIII]

kramer65 schreef op vrijdag 13 mei 2011 @ 17:48:
Maar de vraag is meer; hoe kan je een query in één keer op alle tables in een DB doen?

Niet; althans: niet zover ik weet. En als 't wél kan dan is 't een MySQL-only-iets en niet echt standaard SQL. Daarbij staat er potdikkie in de manual waar ik je op wijs een multi-table delete syntax. Dat je daar geen wildcards voor kunt gebruiken is wiedes, maar kom je er dan niet met het opsommen van alle tabellen? En: waarom heb je überhaupt zo'n databaseontwerp? Want daar zit (de basis van) je probleem, niet in de queries.

kramer65 schreef op vrijdag 13 mei 2011 @ 17:48:
Misschien nog een stapje terug. Als ik mijn eerste query nou op één table wil doen, hoe doe ik die MODIFY dan op twee velden. Ik heb dit geprobeerd:

SQL:

ALTER TABLE aex
MODIFY `Open` decimal(17,2)
AND `Slot` decimal(17,2)

Aan die query alleen al zie ik een (mogelijke, allee dan) ontwerpfout: waarom heb je een table "aex" (en dus vermoedelijk ook een table "nasdaq", "dow_jones", "whatnot")

kramer65 schreef op vrijdag 13 mei 2011 @ 17:48:
Iemand een tipje misschien..

Ja, nogmaals: neem even de moeite je te verdiepen in de materie waar je mee bezig bent. Deze vragen zijn prima door jezelf te beantwoorden als je even de moeite neemt een tutorial of 2 SQL door te nemen en de manual bij de hand te houden als je tegen problemen aan loopt.
Als je dan toch een tip wil voor je query: Die AND hoort er helemaal niet; dat is een operator, geen voegwoord

kramer65 schreef op vrijdag 13 mei 2011 @ 17:48:
--EDIT--
@Roblll
De tables zijn allemaal hetzelfde omdat ze allemaal een beursindex bevatten over de afgelopen jaren. Zo heb ik de table aex waarin alle Open en Slot koersen staan over de laatste decennia. Hetzelfde heb ik voor zo'n 30 andere beursindexen. Zodoende dat ze kwa opzet allemaal hetzelfde zijn..

Nogmaals: Leer normaliseren. Waarom niet gewoon:

code:

Beurs
======

id  name
1   AEX
2   NasDaq
3   Dow Jones
...

Koersen
========

beurs_id    datum       open    slot
1           1-1-1970    1,20    2,11
2           1-1-1970    2,45    3,67
3           1-1-1970    4500    4800
1           2-1-1970    2,11    2,10
...

Twee tabellen en zoveel beursen als je wil. PK van beurs.id maken, en een Composite key van koersen.beurs_id (FK) en koersen.datum maken et voila.

[ Voor 26% gewijzigd door RobIII op 13-05-2011 18:09 ]

[kramer65]

Okee, okee. Ik ga mij eens wat meer verdiepen. Maar nog een vraagje:

Aan die query alleen al zie ik een (mogelijke, allee dan) ontwerpfout: waarom heb je een table "aex" (en dus vermoedelijk ook een table "nasdaq", "dow_jones", "whatnot")

ik heb idd ook een table nasdaq, dowjones, bel20, etc.. Wat is daar mis mee?

[RobIII]

Nor-ma-li-se-ren. Pas 5x gezegd ofzo. Ik geef je notabene een voorbeeld hoe je in twee tabellen zoveel beursen kunt opslaan als je wil. Had je dat toegepast dan had je het probleem uit je topicstart ook nooit gehad. Simple as that.

[ Voor 72% gewijzigd door RobIII op 13-05-2011 18:19 ]

[kramer65]

Okee. Ik roep redelijk wat frustraties op met mijn estupiditeiten, dat moge duidelijk zijn. Snorrie daarvoor. Ik weet wat normalisatie is, maar het probleem is dat de data (als in; datums) niet voor elke index hetzelfde zijn. Door weekenden en vooral feestdagen zijn er namelijk in elke table wel dagen die in andere tables niet voorkomen en andersom. Daarbij komt dat ik de data (als in; gegevens) in 25 aparte excelsheets heb gekregen omdat ze zo uit de Datastream database kwamen. Daarom heb ik ze nu eerst geimporteerd in een mysql db waarna ik de boel nu moet gaan schoonmaken.

Zouden jullie het verschil in data (datums) geen goede reden vinden om de tables gescheiden te houden?

[RobIII]

Hoezo verschil in datums? Een datum is een datum, basta. Heb je mijn voorbeeld überhaupt bekeken? En wat dan nog als er een dag mist voor beursX ?

Nee, sorry maar het gros van wat je post slaat als een tang op een varken. Ik wil je nogmaals adviseren toch wat meer (basis)kennis op te doen voordat je hiermee verder gaat.

[ Voor 37% gewijzigd door RobIII op 13-05-2011 18:33 ]

[kramer65]

Okee. Ik ga mij even in een hoekje schamen (is niet ironisch bedoelt, maar serieus) en mij eerst eens even goed inlezen.. Nogmaals sorry voor alle vragen, en alsnog bedankt voor de reacties..

[Cruz]

Merk op dat het soms om performance redenen handiger kan zijn om juist niet te normaliseren en dus wel aparte tabellen aan te houden per index.

(Maar dit lijkt hier zeker niet het geval te zijn! )

[ Voor 16% gewijzigd door Cruz op 13-05-2011 18:47 ]

[kramer65]

Cruz schreef op vrijdag 13 mei 2011 @ 18:42:
Merk op dat het soms om performance redenen handiger kan zijn om juist niet te normaliseren en dus wel aparte tabellen aan te houden per index.

Dat zou in dit geval best wel een reden kunnen zijn. Ik heb deze database al een keer eerder opgebouwd en had toen redelijke problemen met sommige queries (met 3 lagen subqueries). Mijn webhost konden die niet aan en op mijn eigen laptop (nieuwste macbook pro) deden sommige queries meer dan drie kwartier over.

Ik zeg dit overigens niet om nog een flauw gelijkje te krijgen. Het is serieus zo..

De reden dat ik deze DB nu overigens opnieuw opbouw is dat ik niet zeker was van de betrouwbaarheid van de gegevens.

[ Voor 21% gewijzigd door kramer65 op 13-05-2011 18:48 ]

[Moraelyn]

In dit geval zul je daar geen last van hebben. Stel 40 jaar * 365 * 10 beursen = 146000 rijen. Dat stelt dus niks voor in 1 tabel. Vraag ik me af wat voor queries je zit te maken wil dat zo lang duren. Dan nog zou je geen aparte tabellen aanmaken, maar eerder de data partitioneren over meerdere disks of iets dergelijks. Tenzij je het hebt over bv. datamarts, maar dat is een ander verhaal.

Overigens kun je in SQL Server via sys.columns wel meerdere kolommen van meerdere tabellen tegelijk aanpassen. Weet niet of dit ook in MySQL zit.

[Manuel]

De tabelstructuur die je nou hebt uitgekozen is verre van optimaal, een koppeltabel zou beter zijn geweest.

Maar goed om even in de voorkauw modus te springen. Je kunt dit 'quick-and-dirty' oplossen door het volgende te doen (pseudo code):
[ol]
• Maak een array met de tabelnamen, iets als: [ 'dow_jones', 'aex', etc. ]
• Doorloop deze array en maak een aantal statements. (ALTER TABLE naam_array[index] MODIFY Open ....)
• Voer deze statements uit op je database en voilla.


Het is een lelijke oplossing, maar het doet wat het moet doen, althans voor nou. Het beste zou zijn de database eens uit te tekenen en goed het voorbeeld van RobIII bestuderen hoe dat precies werkt, voor meer informatie zie:

RobIII schreef op vrijdag 13 mei 2011 @ 17:54:
Nogmaals: Leer normaliseren. Waarom niet gewoon:

Twee tabellen en zoveel beursen als je wil. PK van beurs.id maken, en een Composite key van koersen.beurs_id (FK) en koersen.datum maken et voila.

[ Voor 4% gewijzigd door Manuel op 13-05-2011 19:24 ]

[Gomez12]

RobIII schreef op vrijdag 13 mei 2011 @ 17:54:
[...]

Niet; althans: niet zover ik weet.

Ach, met stored procedures kan het wel hoor, als je het echt,echt,echt wilt...

Geen idee of het ook mysql kan, maar op een sql-server testbak heb ik het hier gewoon werken (complete test-omgeving rebuilden moet vaak gebeuren en dan waren de internal statistics niet altijd even bijgewerkt )

Gewoon over alle (test-) dbases itereren en daarbinnen over alle tables itereren.

Ik zou het enkel voor testdingen adviseren (zo af en toe krijg je namelijk wel eens wat FUBAR dingen vanwege locks die extreem lang kunnen worden)

[RobIII]

Gomez12 schreef op vrijdag 13 mei 2011 @ 21:28:
[...]

Ach, met stored procedures kan het wel hoor, als je het echt,echt,echt wilt...

Ja, en door wat in de systeemtabellen te rommelen kan 't ook. Of met een stukje script. En op 1001 andere manieren. Je kent me goed genoeg om te weten dat ik dat ook wel weet, daar loop je lang genoeg voor rond hier Punt is dat je hier verre van wil blijven. Je slaat geen spijker in de muur met een glazen fles. En wat TS aan 't doen is is nog een graadje erger: die probeert een dropveter in de muur te slaan met een schoen

kramer65 schreef op vrijdag 13 mei 2011 @ 18:47:
[...]


Dat zou in dit geval best wel een reden kunnen zijn. Ik heb deze database al een keer eerder opgebouwd en had toen redelijke problemen met sommige queries (met 3 lagen subqueries). Mijn webhost konden die niet aan en op mijn eigen laptop (nieuwste macbook pro) deden sommige queries meer dan drie kwartier over.

Ik zeg dit overigens niet om nog een flauw gelijkje te krijgen. Het is serieus zo..

Tenzij je een heleboel informatie achterwege hebt gelaten in je topicstart (en dat zou niet moeten volgens onze Quickstart) ga ik er toch even, _{niet om mijn flauw gelijk te halen} , van uit dat je in de eerdere opzet ook al behoorlijke ontwerpfouten hebt gemaakt Zoals Moraelyn al aangeeft: met decennia aan data van tig beursen begin je net het "miljoen records" aantal te kietelen; daar wordt een béétje RDBMS (en ook MySQL ) echt niet warm of koud van. En tenzij je allerlei rare stunts aan 't uithalen bent kan ik me niet voorstellen dat je queries 3 kwartier zouden duren; als dat wel zo is heb je ergens een fout gemaakt.

[ Voor 52% gewijzigd door RobIII op 14-05-2011 00:49 ]

[cariolive23]

kramer65 schreef op vrijdag 13 mei 2011 @ 18:47:
[...]


Dat zou in dit geval best wel een reden kunnen zijn. Ik heb deze database al een keer eerder opgebouwd en had toen redelijke problemen met sommige queries (met 3 lagen subqueries). Mijn webhost konden die niet aan en op mijn eigen laptop (nieuwste macbook pro) deden sommige queries meer dan drie kwartier over.

Ik zeg dit overigens niet om nog een flauw gelijkje te krijgen. Het is serieus zo..

De database kan jou laten zien hoe een query wordt uitgevoerd en waar de meeste tijd in gaat zitten. MySQL gebruikt hiervoor EXPLAIN, zet dit voor jouw query en zie de resultaten. Zonder de resultaten van EXPLAIN te kennen, kun jij alleen maar gokken waar het probleem zou kunnen zitten.

Gezien jouw kennis van databases, normaliseren en SQL, is er een grote kans dat je zelf iets fout hebt gedaan. Ga normaliseren en eenvoudige queries opzetten, wanneer je dat onder de knie hebt, kun je met de complexe zaken aan de slag gaan.

Succes!