Query uitbreiden

Het was even kijken, maar zelf zit ik aan een subquery met INSERT te denken:

INSERT INTO prstshp_collect_productstype_packing (producttype, quantity)
SELECT soort, SUM(AantalInSoort)
FROM (.............

En dit op het einde:

ON DUPLICATE KEY UPDATE quantity = quantity + VALUES(quantity); 

Ik neem aan dat `soort` en `AantalInSoort` overeenkomen met de kolommen `producttype` en `quantity` in de tabel `prstshp_collect_productstype_packing` (waaruit ik afleid dat je PrestaShop gebruikt?)

WITH nieuw AS (  -- rekent tussentijds resultaat uit
  SELECT soort, AantalInSoort
  FROM (<je query>) AS bron
    LEFT JOIN prstshp_collect_productstype_packing AS doel
    ON bron.soort = doel.producttype
  WHERE doel.id IS NULL  -- rij is niet aanwezig in doeltabel
)
INSERT INTO prstshp_collect_productstype_packing (producttype, quantity)
SELECT soort, AantalInSoort
FROM nieuw

Dit voegt de regels toe die nog niet voorkomen zoals je vroeg.

Het tussentijdse resultaat is van belang omdat je niet tegelijkertijd data kan selecteren uit de tabel die je gaat wijzigen.

- Ariën - en Ad Fundum bedankt voor het antwoord. Ik heb een hoop van jullie geleerd.

En ja ik gebruik Prestashop. Maar ik heb er wel wat tabellen naast gebouwd en wat php pagina's om onze verzending beter te stroomlijnen. Ik wil nu per order bekijken welke verpakking in nodig heb. Het vullen van de eerder genoemde tabel is daar ook een onderdeel van.

producttype is van het type text met utf8_general_ci
quantity is van het type int(11)

Ik weet niet of dat dat juist is als ik het vergelijk met de query. Maar ik denk van wel.


[size=xsmall]Toevoeging op 05/10/2023 13:32:59:[/size]

Ik probeer hem eerst uit voor ik het in php code ga zetten.
In PHPMyAdmin krijg ik de volgende foutmelding:

#1064 - Er is iets fout in de gebruikte syntax bij 'INSERT INTO prstshp_collect_productstype_packing (producttype, quantity)
SEL...' in regel 55

de code zoals ik die nu heb is als volgt:

WITH nieuw AS (  -- rekent tussentijds resultaat uit
  SELECT soort, AantalInSoort
  FROM (
 			SELECT
                IF (SUBSTRING(prstshp_product_lang.name, 1, 2) = '1 ',
                    
                    LOWER( SUBSTRING_INDEX(
                    SUBSTRING(
                        prstshp_product_lang.name,
                        LENGTH( SUBSTRING_INDEX( prstshp_product_lang.name, ' ', 2 ) ) + 2,
                        LENGTH( prstshp_product_lang.name ) - LENGTH( SUBSTRING_INDEX( prstshp_product_lang.name, ' ', 2 ) )
                    ), ' - ', 1 ) ),
                    
                      LOWER( SUBSTRING_INDEX( prstshp_product_lang.name, ' - ', 1 ) )
                    
                ) AS soort,

                SUM(
                    IF (prstshp_product.product_type = 'pack',
                        prstshp_order_detail.product_quantity * prstshp_pack.quantity,
                        prstshp_order_detail.product_quantity
                    )
                ) AS AantalInSoort
                
                FROM prstshp_orders
                LEFT JOIN prstshp_order_detail
                    on prstshp_order_detail.id_order = prstshp_orders.id_order
                    
                LEFT JOIN prstshp_pack
                    on prstshp_pack.id_product_pack = prstshp_order_detail.product_id

                LEFT JOIN prstshp_product
                    ON (prstshp_product.id_product = prstshp_order_detail.product_id AND prstshp_product.product_type = '')
                    OR (prstshp_product.id_product = prstshp_pack.id_product_item )
                    
                LEFT JOIN prstshp_product_lang
                    on prstshp_product_lang.id_product = prstshp_product.id_product
                    AND prstshp_product_lang.id_lang = 1
                    AND prstshp_product_lang.id_shop = prstshp_orders.id_shop
                    
                WHERE current_state=2 
					OR current_state=3
					OR current_state=11
					OR current_state=20
                
               GROUP BY
                    prstshp_orders.id_order, soort
  ) AS bron
    
    LEFT JOIN prstshp_collect_productstype_packing AS doel
    ON bron.soort = doel.producttype
    
  WHERE doel.id IS NULL  -- rij is niet aanwezig in doeltabel
)
INSERT INTO prstshp_collect_productstype_packing (producttype, quantity)
SELECT soort, AantalInSoort 
FROM nieuw;

Frits van Leeuwen op 05/10/2023 12:16:26

producttype is van het type text met utf8_general_ci
quantity is van het type int(11)

Kan je negeren tot het misgaat; MySQL / MariaDB doen net als PHP aan impliciete type casting, dwz. ze passen het datatype aan naar gelang de producten denken dat het goed is, ook al is dat soms niet precies wat je bedoelt (en dan pas moet je bijsturen).
Als je op mijn spoor zit en liever voorkomt dan geneest, moet je een andere database als PostgreSQL gebruiken, of zelf heel scherp in de gaten houden wat MySQL / MariaDB aan het doen is. En dat is geen doen.

mis je niet een ; voor INSERT?

In het voorstel van "Ad Fundum" staat die ook niet.
Ik ben nog niet zo thuis in SQL dat ik deze variant ken, maar aangezien het "nieuw" als een tabel in het geheugen wordt gemaakt en vervolgens in de INSERT wordt gebruikt, lijkt mij dat hier geen ; moet. Maar misschien dat "Ad Fundum" dit beter kan verklaren.

Even een reactie tussendoor.
De syntax in mijn vorige vorige post heet CTE (Common Table Expressions), en kan je gebruiken om queries te versimpelen en om zogenaamde recursieve queries te maken.
In mijn post gebruik ik het, omdat het resultaat van een CTE eerst wordt uitgerekend door de database, waarna je het resultaat kan gebruiken in een andere query. Zonder dat het tussentijdse resultaat nog verandert.

Zie ook:
- https://www.mysqltutorial.org/mysql-cte/
- https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions
- https://stackoverflow.com/questions/15574281/using-the-with-clause-in-an-insert-statement

Ad Furdum, bedankt voor de verduidelijking. In tussen ben ik ook opzoek gegaan naar het WITH statement op deze manier. En vond er ook wel iets over.

Waar ik nu over struikel is regel 55. Er zou iets fout zijn, maar ik heb geen idee wat. Want als ik die regel weg laat, vervolgt hij met de SELECT regel en dan krijg ik keurig een lijstje. Maar ik wil natuurlijk de INSERT INTO regel wel er in hebben. Anders krijg ik mijn doel niet. of ik moet het met PHP gaan oplossen. Dat zou jammer zijn.

Ik moet m'n eigen linkjes beter lezen - in tegenstelling tot wat alle andere databases doen ondersteunt MySQL de constructie WITH .. INSERT INTO niet.
Dus mijn suggestie werkt niet in MySQL.

Ik zou nog kunnen opperen om het het herschrijven zonder WITH:

INSERT INTO prstshp_collect_productstype_packing (producttype, quantity)
SELECT soort, AantalInSoort
FROM (
  SELECT soort, AantalInSoort
  FROM (<je query>) AS bron
    LEFT JOIN prstshp_collect_productstype_packing AS doel
    ON bron.soort = doel.producttype
  WHERE doel.id IS NULL
) AS nieuw

Maar ik kan daarvan niet inschatten of dat het gewenste resultaat oplevert omdat je tegelijkertijd leest en schrijft naar dezelfde tabel. Dus het beste dat ik je kan aanraden is om het in PHP te doen omdat MySQL het niet kan.

[size=xsmall]Toevoeging op 06/10/2023 17:10:10:[/size]

Oké, dat is wel pessimistisch gesteld. Je kunt (zoals alles in MySQL) het werk zelf doen en zelf een tijdelijke tabel aanmaken met het tussenresultaat.

CREATE TEMPORARY TABLE nieuw AS
SELECT soort, AantalInSoort
  FROM (<je query>) AS bron
    LEFT JOIN prstshp_collect_productstype_packing AS doel
    ON bron.soort = doel.producttype
  WHERE doel.id IS NULL;

En daarna:

INSERT INTO prstshp_collect_productstype_packing (producttype, quantity)
SELECT soort, AantalInSoort FROM nieuw;

Om veilig te zitten zou je beide queries in een transactie moeten zetten:

START TRANSACTION;
<eerste query>
<tweede query>
COMMIT;

Maar het was leuker geweest als MySQL (net als alle andere databases) gewoon WITH ... INSERT INTO had ondersteund.

Heel erg bedankt dat je de moeite neemt voor het helpen oplossen van het probleem.
Ik kan me voorstellen (na jouw uitleg) dat dit ook niet gaat werken. Dat moet dan inderdaad zijn omdat je schrijft en leest in de zelfde tabel.
Ik had eerder ook aan deze oplossing gedacht, maar dat lukte toen ook niet. Maar dat kan zijn omdat mijn insteek fout was.

Nu lukt het wel. Hartelijk dank.

De code die uiteindelijk werkt is:

INSERT INTO prstshp_collect_productstype_packing (producttype, quantity)
SELECT soort, aantalinsoort
FROM (
  SELECT soort, aantalinsoort
  FROM (
   -- start bron
   SELECT
			IF (SUBSTRING(prstshp_product_lang.name, 1, 2) = '1 ',
				LOWER( SUBSTRING_INDEX(
                	SUBSTRING(
                    	prstshp_product_lang.name,
                    	LENGTH( SUBSTRING_INDEX( prstshp_product_lang.name, ' ', 2 ) ) + 2,
                     	LENGTH( prstshp_product_lang.name ) - LENGTH( SUBSTRING_INDEX( prstshp_product_lang.name, ' ', 2 ) )
                	), ' - ', 1 ) ),
				LOWER( SUBSTRING_INDEX( prstshp_product_lang.name, ' - ', 1 ) )
			) AS soort,

			SUM(
				IF (prstshp_product.product_type = 'pack',
                	prstshp_order_detail.product_quantity * prstshp_pack.quantity,
                	prstshp_order_detail.product_quantity
                )
          	) AS aantalinsoort

        FROM prstshp_orders
          	LEFT JOIN prstshp_order_detail
          		on prstshp_order_detail.id_order = prstshp_orders.id_order

          	LEFT JOIN prstshp_pack
          		on prstshp_pack.id_product_pack = prstshp_order_detail.product_id

          	LEFT JOIN prstshp_product
          		ON (prstshp_product.id_product = prstshp_order_detail.product_id AND prstshp_product.product_type = '')
          		OR (prstshp_product.id_product = prstshp_pack.id_product_item )

          	LEFT JOIN prstshp_product_lang
          		on prstshp_product_lang.id_product = prstshp_product.id_product
          		AND prstshp_product_lang.id_lang = 1
          		AND prstshp_product_lang.id_shop = prstshp_orders.id_shop

        WHERE current_state=2
        	OR current_state=3
       		OR current_state=11
        	OR current_state=20

        GROUP BY
        	prstshp_orders.id_order, soort  
      
  ) AS bron
    
    LEFT JOIN prstshp_collect_productstype_packing AS doel
    ON bron.soort = doel.producttype
    
  WHERE doel.id IS NULL
  GROUP BY soort, aantalinsoort  
) AS nieuw