MySQL - Vergelijken 2x COUNT, daarna update uitvoeren

[sus]

Mijn vraag

Ik heb 3 tabellen.

1) Tabel met productfilters (Shop_Product_Filters)
2) Tabel met producten (Shop_Product_Meta)
3) Tabel met o.a. kolommen 'itemcode' en 'filters'

Wat ik wil is dat ik met een query een telling doe van alle productfilters in een groep, een telling doe van alle productfiltes aan een artikel. Die 2 waardes met elkaar vergelijk en van daaruit in tabel 3 een waarde true of false zet bij het artikelnummer

Relevante software en hardware die ik gebruik
Liefst alleen MySQL. Met PHP erbij heb ik de boel al werkend, dat is geen uitdaging. Maar het liefst zou ik dit in 1x draaien. Het gaat om een beste lijst met producten en filters, een php-script is er best even mee bezig. Vanwege de snelheid zou het erg fijn zijn als dit volledig via de DB kan.

Wat ik al gevonden of geprobeerd heb

Deze query doet wat ik wil en geeft 2 waardes. Hiermee draai ik nu in php een vergelijk en voer dan een update-query uit op de 3e tabel.

Wat ik nu dus nog zoek is dat in in MySQL "vergelijk CountedGroupFilters met CountedProductFilters" uitvoer en dan een 'true' of 'false' zet in tabel 3.

SELECT
(SELECT COUNT(*) FROM Shop_Product_Filters WHERE `group` = 'productgroep') as CountedGroupFilters,
(SELECT COUNT(*) FROM Shop_Product_Meta WHERE `itemcode` = 'artikelnummer') as CountedProductFilters;


Wat ik zoek is dus iets als

UPDATE `ProductInfo` SET `filters` = 'false' WHERE CountedGroupFilters <> CountedProductFilters, en dat rondom bovenstaande.


Ik kom er met de howto's niet aan uit en heb geen idee of het uberhaupt kan. Als het niet kan, prima, ik heb het dus al werkend vanuit PHP.

[drm]

Je moet even nadenken hoe je aan je ProductId komt. Vervolgens kun je de bottom-up (kleinste query eerst) de query zo omschrijven dat je de product id ook in de resultaten hebt zitten. Dan gebruik je het resultaat van die query in een subquery in de WHERE van je update.

Dit is even uit de losse pols, maar geeft je wel een idee van de richting.

Omdat je product id ook de verbindende factor is (neem ik aan...) tussen de twee tabellen heb je een join en een group by nodig, die ervoor zorgt dat de counts per product zijn:

SQL:

-- geeft dit alle product ids die ik wil updaten?
SELECT 
   a.ProductId,
   COUNT(DISTINCT a.Id),
   COUNT(DISTINCT b.Id)
FROM 
   Shop_Product_Filters a 
       INNER JOIN Shop_Product_Meta ON a.ProductId=b.ProductId
WHERE 
    a.group = 'productgroep'
    AND b.itemcode = 'artikelnummer'
GROUP BY Id
HAVING COUNT(DISTINCT a.Id) <> COUNT(DISTINCT b.Id)

Of je al dan niet de DISTINCT nodig hebt, moet je ook zelf even bedenken. Bekijk het verschil van de resultaten van de query en bedenk je wat semantisch het verschil is en besluit op basis daarvan of je wel of niet DISTINCT bedoelt.

Dan:

SQL:

UPDATE 
    Product 
SET 
    foo=bar 
WHERE ProductId IN(
    SELECT ProductId FROM (
        -- hier de bovenstaande query
    ) t
);

Belangrijk: als het resultaat van 1 van de twee counts ook 0 kan zijn, heb je de product tabel in je query nodig met een LEFT JOIN voor de twee andere tabellen, omdat de resultaten in de LEFT JOIN dan namelijk NULL opleveren, en een count van NULL is 0. Je wilt dan immers ook alle bestaande producten waarvoor er geen record in (een van) de andere twee tabellen voorkomt. Als dat het geval is moeten de voorwaarden voor de JOIN ook in de ON clause van je JOIN komen te staan. Ik vermoed dat voor 1 van de twee tabellen wel en voor de andere niet het geval is. Aan jou de eer uit te vogelen wat ik daarmee bedoel

Gevolg daarvan is dan weer dat je een temporary table nodig hebt omdat MySQL geen updates ondersteunt op een tabel die in de subquery voorkomt. Dat laat ik mooi als een oefening voor de lezer

Nog wat tips:

• zorg dat je dit soort dingen altijd in transacties doet, zeker als je de temp table nodig hebt. Dat moet je in je PHP script sowieso ook doen, want anders loop je het risico op data inconsistenties; je kunt anders namelijk niet garanderen dat het resultaat tussen de select en de update inmiddels veranderd is.
• je kunt van je select query ook een view maken, dan is het iets beter leesbaar en kun je 't makkelijker hergebruiken. Het is sowieso good practice om je updates eerst als een select te schrijven en dan als je er zeker van bent dat het geupdate kan worden als een update te doen. Door er een view van te maken voorkom je het risico dat je in het omschrijven naar een update een fout maakt waarmee je de logica perongeluk hebt aangepast.

[ Voor 0% gewijzigd door drm op 22-04-2023 15:30 . Reden: Damn, zo lang hier niet meer geweest, ik weet niet meer hoe RML werkt 8)7 ]

[sus]

Yes! En nu je het zo uitwerkt... daar heb ik dus overheen gekeken. M'n SQL is ook wat roestig, is dik 20 jaar geleden dat ik er les in heb gehad en heb er weing mee gedaan sindsdien

Werkt helemaal, update van 6000+ records in 0.00nogwat sec