Useful database queries

Move products to an another category

UPDATE db220385_2.catalog_category_product SET category_id = 18 WHERE category_id = 19;

Get all attribute option values

The following example shows, how to get all available values for the attribute filter_stores

SELECT ea.attribute_code, eao.option_id, eaov.store_id, eaov.value_id, eaov.value 
    FROM eav_attribute 
        AS ea  
    INNER JOIN eav_attribute_option 
        AS eao 
        ON ea.attribute_id = eao.attribute_id
    INNER JOIN eav_attribute_option_value 
        AS eaov 
        ON eao.option_id = eaov.option_id
WHERE ea.attribute_code = 'filter_stores';

Customer – get DOB (Mage 1.10.1.1)

SELECT ce.*, ced.value AS dob FROM `customer_entity` AS ce
LEFT JOIN `customer_entity_datetime` AS ced ON(ce.entity_id = ced.entity_id AND `attribute_id` = 11)
ORDER BY ce.entity_id DESC

Customer – get DOB from orders (Mage 1.10.1.1)

SELECT customer_is_guest, `customer_dob`, `customer_email`, store_name 
FROM `db176735`.`sales_flat_order` 
Order BY `entity_id` DESC;

Change order totals

Update sales_flat_order 
SET 
    base_grand_total = 36.0000, 
    base_subtotal = 30.2500, 
    base_tax_amount = 5.75, 
    grand_total = 36.0000,
    subtotal = 30.2500, 
    tax_amount = 5.7500,
    total_qty_ordered = 3, 
    base_subtotal_incl_tax = 36.0000, 
    subtotal_incl_tax = 36.0000, 
    weight = 30.000 
where entity_id = 424

Get configurable products directly from the database Mage EE 1.10.1.1 / CE 1.5.x)

SELECT
 `t1`.`entity_id`,
 `t1`.`atikelnr`,
 `eaov1`.`value` AS 'size',
 `eaov2`.`value` AS 'color'
FROM `catalog_product_flat_1` AS `t1` 
 
LEFT JOIN  `catalog_product_entity_int` AS `cpei1` ON (
 `t1`.`entity_id` = `cpei1`.`entity_id` 
 AND `cpei1`.`attribute_id` = 606
 AND `cpei1`.`entity_type_id` = 4
 #AND `cpei1`.`entity_id` = 609 #Hier Product ID #Einkommentieren für spezielles Product
)
LEFT JOIN  `catalog_product_entity_int` AS `cpei2` ON (
 `t1`.`entity_id` = `cpei2`.`entity_id` 
 AND `cpei2`.`attribute_id` = 80
 AND `cpei2`.`entity_type_id` = 4
 #AND `cpei2`.`entity_id` = 609 #uncomment and add product id to get a certain product
)
LEFT JOIN `eav_attribute_option_value` AS `eaov1` ON (`cpei1`.`value` = `eaov1`.`option_id`)
LEFT JOIN `eav_attribute_option_value` AS `eaov2` ON (`cpei2`.`value` = `eaov2`.`option_id`)
 
WHERE `t1`.`entity_id` IN (609,610,611) #Hier Product IDs
AND `eaov1`.`store_id` = 1 #Hier die Store ID
AND `eaov2`.`store_id` = 1 #Hier die Store ID

Artikelnr to entity_id

SELECT `e`.entity_id AS `atikelnr` FROM `catalog_product_entity` AS `e`
INNER JOIN `catalog_product_entity_varchar` AS `_table_atikelnr`
ON (`_table_atikelnr`.`entity_id` = `e`.`entity_id`)
AND (`_table_atikelnr`.`attribute_id` = '591')
AND (`_table_atikelnr`.`store_id` = 0)
WHERE (e.type_id = 'configurable')
AND `_table_atikelnr`.`value` IN(35161,35162,35163,35166,35561,35562,35563,35566,33080,33081,33084,33085,33580,33581,33582,33584,33585,13698,16835,16836)

Get the number of new costomers per month since a certain date

SELECT 
    MONTHNAME(ce.created_at), 
    COUNT(*)
FROM `db176735`.`customer_entity` AS ce 
WHERE ce.created_at >= '2011-01-01 00:00:00'
GROUP BY MONTHNAME(ce.created_at);

Change order addresses (Changes over Query-Browser)

SELECT 
    * 
FROM 
    sales_flat_order_address WHERE `parent_id` = 126349; //126349 = order_id in sales flat order
SELECT
  `increment_id`,
  `state`, `status`,
  `customer_prefix`,
  `customer_firstname`,
  `customer_lastname`,
  `customer_email`,
  `send_to_pvs`,
  `amazon_order_ids`
FROM sales_flat_order
WHERE entity_id = 126349
AND increment_id = 100118608;

Get all orders without items

SET 
    @@group_concat_max_len = 10240;
SELECT 
    db176735.sales_flat_order.entity_id AS OrderID,
    db176735.sales_flat_order.customer_email AS Email,
    db176735.sales_flat_order_address.prefix AS Anrede,
    db176735.sales_flat_order_address.firstname AS Vorname,
    db176735.sales_flat_order_address.lastname AS Nachname,
    db176735.sales_flat_order_address.street AS Adresse,
    db176735.sales_flat_order_address.city AS Stadt,
    db176735.sales_flat_order_address.country_id AS Land,
    db176735.sales_flat_order_address.postcode AS PLZ,
    db176735.sales_flat_order_address.telephone AS Telefon,
    db176735.sales_flat_order_address.mobile AS Mobil,
GROUP_CONCAT
    (DISTINCT items.name,' - ', items.sku ORDER BY items.sku SEPARATOR ' | ') AS Prodkte,
        db176735.sales_flat_order.grand_total AS Summe,
        db176735.sales_flat_order.STATUS AS STATUS
FROM 
    db176735.sales_flat_order
INNER JOIN 
    db176735.sales_flat_order_address
        ON db176735.sales_flat_order_address.parent_id = db176735.sales_flat_order.entity_id
INNER JOIN 
    db176735.sales_flat_order_item AS items
        ON items.order_id = db176735.sales_flat_order.entity_id
WHERE 
    db176735.sales_flat_order_address.address_type = 'billing'
AND 
    items.product_type = 'simple'
GROUP BY
    (items.order_id);
SET 
    @@group_concat_max_len = 1024;

Delete Products

This query deletes all products accept the one with the value 10207 for the varchar attribute with the attribute_id 175 (sns_artikelnr):

DELETE cpe.* FROM `database`.`catalog_product_entity` AS cpe
LEFT JOIN `database`.catalog_product_entity_varchar AS cpev ON cpe.entity_id = cpev.entity_id 
WHERE cpev.attribute_id=175 AND cpev.value != 10207

Get customer addresses from database

SELECT
  #count(distinct ce.entity_id),
  cae.entity_id   as address_id,
  IF(cae.entity_id = cei_db.value,'billing',IF(cae.entity_id = cei_ds.value,'shipping','-')) as dafault_address_type,
  #cei_db.value    as default_billing_id,
  #cei_ds.value    as default_shipping_id,
  ce.entity_id    as inernal_customer_id,
  ce.email        as customer_email,
  cev_pf.value    as customer_prefix,
  cev_fn.value    as customer_firstname,
  #cev_mn.value   as customer_middle_name,
  cev_ln.value    as customer_lastname,
  #cev_sf.value   as customer_suffix,
  ced_dob.value   as customer_dob,
  ce.is_active    as custumer_active,
  ce.store_id,
  ce.created_at,
  ce.updated_at,  
  caev_apf.value  as addresss_prefix,
  caev_afn.value  as addresss_firstname,
  #caev_amn.value  as addresss_middlename,
  caev_aln.value  as addresss_lastname,
  caev_aaa.value  as addresss_addition,
  #caev_asf.value  as addresss_suffix,
  #caev_acp.value  as addresss_company,
  caet_ast.value  as addresss_street,
  caev_act.value  as addresss_city,
  caev_arg.value  as addresss_region,
  #caei_argi.value as addresss_region_id
  caev_apc.value  as addresss_postcode,
  caev_aci.value  as addresss_country_id,
  caev_atf.value  as addresss_telephone,
  caev_afa.value  as addresss_fax,
  #caev_amo.value  as addresss_mobile,
  caev_amp.value  as addresss_mobilephone
FROM customer_entity                       AS ce
LEFT JOIN customer_entity_varchar          AS cev_pf    ON ce.entity_id        = cev_pf.entity_id    AND cev_pf.attribute_id      = 4
LEFT JOIN customer_entity_varchar          AS cev_fn    ON ce.entity_id        = cev_fn.entity_id    AND cev_fn.attribute_id      = 5
#LEFT LEFT JOIN customer_entity_varchar    AS cev_mn     ON ce.entity_id       = cev_mn.entity_id   AND cev_mn.attribute_id       = 6
LEFT JOIN customer_entity_varchar          AS cev_ln    ON ce.entity_id        = cev_ln.entity_id    AND cev_ln.attribute_id      = 7
#LEFT JOIN customer_entity_varchar          AS cev_sf    ON ce.entity_id        = cev_sf.entity_id    AND cev_sf.attribute_id      = 8
LEFT JOIN customer_address_entity          AS cae       ON ce.entity_id        = cae.parent_id
LEFT JOIN customer_entity_datetime         AS ced_dob   ON ce.entity_id        = ced_dob.entity_id   AND ced_dob.attribute_id     = 11
LEFT JOIN customer_entity_int              AS cei_db    ON ce.entity_id        = cei_db.entity_id    AND cei_db.attribute_id      = 13 /*default billing*/
LEFT JOIN customer_entity_int              AS cei_ds    ON ce.entity_id        = cei_ds.entity_id    AND cei_ds.attribute_id      = 14 /*default shipping*/
LEFT JOIN customer_address_entity_varchar  AS caev_apf  ON caev_apf.entity_id  = cae.parent_id       AND caev_apf.attribute_id    = 18
LEFT JOIN customer_address_entity_varchar  AS caev_afn  ON caev_afn.entity_id  = cae.parent_id       AND caev_afn.attribute_id    = 19
#LEFT JOIN customer_address_entity_varchar  AS caev_amn  ON caev_amn.entity_id  = cae.parent_id       AND caev_amn.attribute_id    = 20
LEFT JOIN customer_address_entity_varchar  AS caev_aln  ON caev_aln.entity_id  = cae.parent_id       AND caev_aln.attribute_id    = 21
#LEFT JOIN customer_address_entity_varchar  AS caev_asf  ON caev_asf.entity_id  = cae.parent_id       AND caev_asf.attribute_id    = 22
#LEFT JOIN customer_address_entity_varchar  AS caev_acp  ON caev_acp.entity_id  = cae.parent_id       AND caev_acp.attribute_id    = 23
LEFT JOIN customer_address_entity_text     AS caet_ast  ON caet_ast.entity_id  = cae.parent_id       AND caet_ast.attribute_id    = 24
LEFT JOIN customer_address_entity_varchar  AS caev_act  ON caev_act.entity_id  = cae.parent_id       AND caev_act.attribute_id    = 25
LEFT JOIN customer_address_entity_varchar  AS caev_aci  ON caev_aci.entity_id  = cae.parent_id       AND caev_aci.attribute_id    = 26
LEFT JOIN customer_address_entity_varchar  AS caev_arg  ON caev_arg.entity_id  = cae.parent_id       AND caev_arg.attribute_id    = 27
#LEFT JOIN customer_address_entity_int     AS caei_argi ON caei_argi.entity_id = cae.parent_id       AND caei_argi.attribute_id   = 28
LEFT JOIN customer_address_entity_varchar  AS caev_apc  ON caev_apc.entity_id  = cae.parent_id       AND caev_apc.attribute_id    = 29
LEFT JOIN customer_address_entity_varchar  AS caev_atf  ON caev_atf.entity_id = cae.parent_id        AND caev_atf.attribute_id    = 30
LEFT JOIN customer_address_entity_varchar  AS caev_afa  ON caev_afa.entity_id = cae.parent_id        AND caev_afa.attribute_id    = 31
#LEFT JOIN customer_address_entity_varchar  AS caev_amo  ON caev_amo.entity_id  = cae.parent_id       AND caev_amo.attribute_id    = 626
LEFT JOIN customer_address_entity_varchar  AS caev_amp  ON caev_amp.entity_id  = cae.parent_id       AND caev_amp.attribute_id    = 685
LEFT JOIN customer_address_entity_varchar  AS caev_aaa  ON caev_aaa.entity_id  = cae.parent_id       AND caev_aaa.attribute_id    = 686
WHERE cae.entity_id IS NOT NULL 
      AND cev_pf.value IS NOT NULL
      AND caev_apf.value IS NOT NULL
      AND caev_apf.value <> ''
ORDER BY ce.entity_id DESC
#LIMIT 100000;

All orders with items

SELECT
  sfo.entity_id AS OrderID,
  sfo.increment_id AS `Bestellnummer`,
  sfo.status AS status,
  sfo.coupon_code AS CouponCode,
  sfo.customer_email AS Email,
  sfoab.prefix AS `Anrede-Rechnung`,
  sfoab.firstname AS `Vorname-Rechnung`,
  sfoab.lastname AS `Nachname-Rechnung`,
  sfoab.street AS `Adresse-Rechnung`,
  sfoab.city AS `Stadt-Rechnung`,
  sfoab.country_id AS `Land-Rechnung`,
  sfoab.postcode AS `PLZ-Rechnung`,
  sfoab.telephone AS `Telefon-Rechnung`,
  sfoab.mobile AS `Mobil-Rechnung`,
  sfoas.prefix AS `Anrede-Shipping`,
  sfoas.firstname AS `Vorname-Shipping`,
  sfoas.lastname AS `Nachname-Shipping`,
  sfoas.street AS `Adresse-Shipping`,
  sfoas.city AS `Stadt-Shipping`,
  sfoas.country_id AS `Land-Shipping`,
  sfoas.postcode AS `PLZ-Shipping`,
  sfoas.telephone AS `Telefon-Shipping`,
  sfoas.mobile AS `Mobil-Shipping`,
  items.name AS Produktname,
  items.sku AS EAN,
  items.qty_ordered AS `QTY`,
  sfo.subtotal AS `Bestellung Netto`,
  sfo.tax_amount AS `Bestellung Steuer`,
  sfo.subtotal_incl_tax AS `Bestellung Brutto`,
  sfo.base_discount_amount AS `Bestellung Discount`,
  sfo.discount_description AS `Bestellung Discount Beschreibung`, 
  sfo.milesandmore_total AS `Bestellung M&M Amount`,
  sfo.milesandmore_used_miles AS `Bestellung M&M eingesetzte Meilen`,
  sfo.shipping_amount AS `Bestellung Versandkosten`,
  sfo.grand_total AS `Bestellung Gesamtsumme`,
  sfo.order_currency_code AS `Bestellung Waerung`,
  sfo.snsmilesandmore_cardnr  AS `M&M Kartennummer`,
  sfo.milesandmore_transaction AS `M&M Transaction`,
  sfo.amazon_marketplace_id  AS `Amazon Marktplatz Id`,
  sfo.amazon_merchant_id  AS `Amazon Merchant ID`,
  sfo.amazon_order_ids  AS `Amazon Order IDs`,
  sfo.amazon_merchant_id  AS `Amazon Merchant ID`,
  sfo.amazon_merchant_id  AS `Amazon Merchant ID`,
  sfo.created_at  AS `Bestelldatum`
FROM
  sales_flat_order AS sfo
  LEFT JOIN
    sales_flat_order_address AS sfoab
      ON sfoab.parent_id = sfo.entity_id AND  sfoab.address_type = 'billing'
  LEFT JOIN
    sales_flat_order_address AS sfoas
      ON sfoas.parent_id = sfo.entity_id AND  sfoas.address_type = 'shipping'
  LEFT JOIN
    sales_flat_order_item AS items
      ON items.order_id = sfo.entity_id
WHERE
    items.product_type = 'simple'
AND
    sfo.created_at > '2014-12-01 00:00:00';

Leave a Reply

Your email address will not be published.

 

This site uses Akismet to reduce spam. Learn how your comment data is processed.