SQL Tips & Tricks

Article Version

5.5.0 or newer

Table of contents

We recommend to only execute those statements with the necessary knowledge.
The provided information were tested at the release of this article, but can be change during the releases of Shopware. The application of this tutorial appears on your own risk! 

Make a Backup of your shop before your start that you can use in case of failure. Keep in mind, that this is adapting Shopware an this is not officially supported!

In this article you find some useful SQL-statements that help you for some use cases. For the access to the database we would recommend the administration tool phpMyAdmin. This allows a graphical view of the database and makes it easier to execute the statements. Of course the given statements can be used with any other MySQL-client as well.

Customer data

Filter duplicate customer numbers

In case you have duplicate customer numbers in your system there can be errors and inconsistent datasets. With this statement you can display the duplicate customer numbers.


SELECT userID, COUNT( userID ) AS number
FROM `s_user_billingaddress`
GROUP BY `userID`
HAVING (COUNT( userID ) >1
)

Item data

Filter duplicate item numbers

In the backend the creation of items with an already used item number is not possible. However interfaces or import-scripts can bypass this mechanism, because of that it might come to errors. This statement allows you to easily find double items.


SELECT ordernumber, COUNT( ordernumber ) AS number 
FROM `s_articles_details`
GROUP BY `ordernumber`
HAVING (COUNT( ordernumber ) >1
);

Filter duplicate SEO-URL's in s_core_rewrite_urls

If a specific SEO-link in your shop can not be generated, the id is often already in use. This statement sorts the entries and shows you possible duplicates.


SELECT *
FROM `s_core_rewrite_urls`
WHERE main =1
GROUP BY `org_path` , `main` , `subshopID`, `path`
HAVING COUNT( * ) >1 

Filter active products which are still not shown in the frontend

In some cases (often because of a wrong synchronizing of the ERP system) active items are not displayed in the frontend. The cause of this can be that the entry in the s_articles table has the value 1 for the active column, but the associated entry in the s_articles_details has the value 0 for this column. This statement shows you the affected items.


SELECT a.name, ad.ordernumber, a.active AS active1, ad.active AS active2
FROM `s_articles` AS a 
JOIN s_articles_details AS ad 
ON a.id = ad.articleID
WHERE a.active <> ad.active

Shop broken items without entry in the s_articles_details table

Sometimes items are created broken in the database by using plugins or interfaces. One clue for this could be a missing entry in the s_articles_details table. This statements shows such broken items, which should be recreated completely anew immediately.


SELECT *
FROM s_articles a
LEFT JOIN
s_articles_details ad
ON ad.articleID = a.id
WHERE ad.id IS NULL

Remove broken variants / variant-corpses

With this statement you can identify or remove variant options that are (no longer) connected. Those can cause inconsistent data and missing, wrong item displays in the frontend.


DELETE d, at
-- SELECT d.*
FROM s_articles_details d
JOIN s_articles a
ON d.articleID = a.id
AND a.configurator_set_id IS NOT NULL
JOIN s_articles_attributes at
ON d.id = at.articledetailsID
LEFT JOIN s_article_configurator_option_relations cr
ON cr.article_id = d.id
WHERE cr.id IS NULL
AND d.articleID = a.id;
 
DELETE r
-- SELECT r.*
FROM `s_article_configurator_option_relations` r
LEFT  JOIN s_articles_details d ON d.id = r.article_id
WHERE d.id IS NULL;

Set variant preselection

With this statement variant items, that do not have a preselection, get the first variant as a preselection automatically. Without a preselection the variant item is not shown in the frontend properly.


UPDATE s_articles a
LEFT JOIN s_articles_details d
ON d.id = a.main_detail_id
SET a.main_detail_id = (
  SELECT id FROM s_articles_details WHERE articleID = a.id LIMIT 1
)
WHERE d.id IS NULL;
UPDATE s_articles a, s_articles_details d
SET d.kind = 1
WHERE d.id = a.main_detail_id;

Fix missing category assignment in the table s_article_categories_ro

If this assignment is missing items are shown as "not available" in the frontend - this statement fills the given table anew for all items. Especially for many items this has a higher performance than recreating the category-tree.


INSERT IGNORE INTO `s_articles_categories_ro` (`articleID`,
`categoryID`, `parentCategoryID`)
 
SELECT ac.articleID, c2.id AS categoryID, c.id AS parentCategoryID
FROM s_articles_categories ac
 
JOIN s_categories c
ON c.id = ac.categoryID
 
JOIN s_categories c2
ON c2.id = c.id
OR c.path LIKE CONCAT('%|', c2.id, '|%')
 
ORDER BY  ac.articleID, c.id, c2.id

This allows you to correct missing preselected images in different areas (item view in the backend, suggestion search, and more). This is sometimes necessary after an update from Shopware 4 to Shopware 5.


UPDATE s_articles_img i, s_media m
SET i.media_id = m.id
WHERE m.path = CONCAT('media/image/', i.img, '.', i.extension);

Deleting property options not used in any products

This query deletes all property options and the corresponding attributes that are currently not linked to a product / unused in the shop.


DELETE v, va
FROM `s_filter_values` v 
LEFT JOIN
s_filter_articles a
ON a.valueID = v.id
JOIN s_filter_values_attributes va
ON v.id = va.valueID
WHERE a.valueID IS NULL

Orders

Display duplicate ordernumbers

If you have double ordernumbers in shopware you get an error in the backend and because of this you should not have those double ordernumbers. This might still occur because of plugins or interfaces. This statement displays you those double ordernumbers.


SELECT ordernumber, COUNT(ordernumber) AS amount
FROM s_order
GROUP BY ordernumber
HAVING ( COUNT(ordernumber) > 1 )

Alternatively you can also copy the orders in a temporary order table and mark the double ordernumbers in the original table.


CREATE TABLE `s_order_tmp` LIKE `s_order`;

INSERT INTO `s_order_tmp` SELECT * FROM `s_order`;

UPDATE s_order
SET ordernumber = CONCAT(ordernumber , '-doppelt')
WHERE id IN (SELECT max(id) FROM s_order_tmp GROUP BY ordernumber
HAVING ( COUNT(ordernumber) > 1 )) AND ordernumber <> 0;

DROP TABLE `s_order_tmp`;  

In this case there is a "-double" added to the double ordernumbers.

Please mind, that the ordernumber "0" marks cancelled carts and this number may occur multiple times without causing any problem.

Migrate customers and orders of a database backup

It might occur that because of a problem or the usage of test-systems some orders are not in the live-database,but the ids of the orders are already in use by new orders. Currently it is not possible to migrate such orders and customers out of a backup database, but this query should help you with this.

Please mind that your system might contain further columns, tables or needs you might have to add. Also payments might have to be controlled manually. Afterwards you should also execute the above query for double ordernumbers so the ordernumbers can be checked after the migration in the new backend.

DBOLDNAME has to be replaced by the database name you want to migratate the data from and the IdOfFirstCustomerToMigrate and NumberOfFirstOrderToMigrate by the ordernumber/id of the first element you want to migrate.


ALTER TABLE s_user ADD idold INT(11) NULL AFTER customernumber;
INSERT INTO s_user  (idold, password, encoder, email, active, accountmode, cONfirmatiONkey, paymentID, firstlogin, lastlogin, sessiONID, newsletter, validatiON, affiliate, customergroup, paymentpreset, LANGUAGE, subshopID, referer, pricegroupID, internalcomment, failedlogins, lockeduntil, default_billing_address_id, default_shipping_address_id, title, salutatiON, firstname, lastname, birthday, customernumber) SELECT * FROM DBOLDNAME.s_user WHERE id >= IdOfFirstCustomerToMigrate;
INSERT INTO s_user_attributes (userID) SELECT id FROM s_user WHERE id NOT IN (SELECT userid FROM s_user_attributes);
INSERT INTO s_user_addresses (user_id, company, department, salutatiON, title, firstname, lastname, street, zipcode, city, country_id, state_id, ustid, phone, additional_address_line1, additional_address_line2) SELECT u.id, oldad.company, oldad.department, oldad.salutatiON, oldad.title, oldad.firstname, oldad.lastname, oldad.street, oldad.zipcode, oldad.city, oldad.country_id, oldad.state_id, oldad.ustid, oldad.phone, oldad.additional_address_line1, oldad.additional_address_line2 FROM DBOLDNAME.s_user_addresses AS oldad JOIN s_user AS u ON u.idold = oldad.user_id WHERE u.idold >=1;
INSERT INTO s_user_addresses_attributes (address_id) SELECT id FROM s_user_addresses WHERE id NOT IN (SELECT address_id FROM s_user_addresses_attributes);
INSERT INTO `s_user_billingaddress` (`userID`, `company`, `department`, `salutatiON`, `firstname`, `lastname`, `street`, `zipcode`, `city`, `phone`, `countryID`, `stateID`, `ustid`, `additional_address_line1`, `additional_address_line2`, `title`) SELECT u.id, oldbil.company, oldbil.department, oldbil.salutatiON, oldbil.firstname, oldbil.lastname, oldbil.street, oldbil.zipcode, oldbil.city, oldbil.phone, oldbil.countryid, oldbil.stateid, oldbil.ustid, oldbil.additional_address_line1, oldbil.additional_address_line2, oldbil.title  FROM DBOLDNAME.s_user_billingaddress AS oldbil JOIN s_user AS u ON u.idold = oldbil.userid WHERE u.idold >=1;
INSERT INTO s_user_billingaddress_attributes (billingID) SELECT id FROM s_user_billingaddress WHERE id NOT IN (SELECT billingID FROM s_user_billingaddress_attributes);
INSERT INTO `s_user_shippingaddress` (`userID`, `company`, `department`, `salutatiON`, `firstname`, `lastname`, `street`, `zipcode`, `city`, `countryID`, `stateID`, `additional_address_line1`, `additional_address_line2`, `title`) SELECT u.id, oldship.company, oldship.department, oldship.salutatiON, oldship.firstname, oldship.lastname, oldship.street, oldship.zipcode, oldship.city, oldship.countryid, oldship.stateid, oldship.additional_address_line1, oldship.additional_address_line2, oldship.title  FROM DBOLDNAME.s_user_shippingaddress AS oldship JOIN s_user AS u ON u.idold = oldship.userid WHERE u.idold >=1;
INSERT INTO s_user_shippingaddress_attributes (shippingID) SELECT id FROM s_user_shippingaddress WHERE id NOT IN (SELECT shippingID FROM s_user_shippingaddress_attributes);
UPDATE s_user AS u JOIN s_user_addresses AS ua ON u.id = ua.user_ID SET u.default_billing_address_id = ua.id WHERE u.idold >=1;
UPDATE s_user AS u JOIN s_user_addresses AS ua ON u.id = ua.user_ID SET u.default_shipping_address_id = ua.id WHERE u.idold >=1;
ALTER TABLE s_order ADD idold INT(11) NULL AFTER deviceType;
ALTER TABLE s_order_details ADD didold INT(11) NULL AFTER pack_unit;
INSERT INTO s_order (idold, ordernumber, userID, invoice_amount, invoice_amount_net, invoice_shipping, invoice_shipping_net, ordertime, STATUS, cleared, paymentID, transactiONID, comment, customercomment, internalcomment, net, taxfree, partnerID, temporaryID, referer, cleareddate, trackingcode, LANGUAGE, dispatchID, currency, currencyFactor, subshopID, remote_addr, deviceType) SELECT o.id, o.ordernumber, u.id, o.invoice_amount, o.invoice_amount_net, o.invoice_shipping, o.invoice_shipping_net, o.ordertime, o.STATUS, o.cleared, o.paymentID, o.transactiONID, o.comment, o.customercomment, o.internalcomment, o.net, o.taxfree, o.partnerID, o.temporaryID, o.referer, o.cleareddate, o.trackingcode, o.LANGUAGE, o.dispatchID, o.currency, o.currencyFactor, o.subshopID, o.remote_addr, o.deviceType FROM DBOLDNAME.s_order AS o JOIN s_user AS u ON u.idold = o.userID WHERE o.ordernumber >= NumberOfFirstOrderToMigrate;
INSERT INTO s_order_attributes (orderID, attribute1, attribute2, attribute3, attribute4, attribute5, attribute6) 
SELECT o.id, oold.attribute1, oold.attribute2, oold.attribute3, oold.attribute4, oold.attribute5, oold.attribute6 FROM s_order AS o JOIN DBOLDNAME.s_order_attributes AS oold ON o.idold = oold.id WHERE o.id NOT IN (SELECT orderID FROM s_order_attributes);
INSERT INTO s_order_details (didold, orderID, ordernumber, articleID, articleordernumber, price, quantity, name, STATUS, shipped, shippedgroup, releasedate, modus, esdarticle, taxID, tax_rate, cONfig, ean, unit, pack_unit)
SELECT posold.id, o.id, posold.ordernumber, posold.articleID, posold.articleordernumber, posold.price, posold.quantity, posold.name, posold.STATUS, posold.shipped, posold.shippedgroup, posold.releasedate, posold.modus, posold.esdarticle, posold.taxID, posold.tax_rate, posold.cONfig, posold.ean, posold.unit, posold.pack_unit FROM s_order AS o JOIN DBOLDNAME.s_order_details AS posold ON o.idold = posold.orderID WHERE o.idold >= 1;
INSERT INTO s_order_details_attributes (detailID, attribute1, attribute2, attribute3, attribute4, attribute5, attribute6)
SELECT d.id, dold.attribute1, dold.attribute2, dold.attribute3, dold.attribute4, dold.attribute5, dold.attribute6 FROM DBOLDNAME.s_order_details_attributes AS dold JOIN s_order_details AS d ON d.didold = dold.id WHERE d.id NOT IN (SELECT detailID FROM s_order_details_attributes);
INSERT INTO s_order_billingaddress (userID, orderID, company, department, salutatiON, customernumber, firstname, lastname, street, zipcode, city, phone, countryID, stateID, ustid, additional_address_line1, additional_address_line2, title) SELECT u.id, o.id, oldbil.company, oldbil.department, oldbil.salutatiON, oldbil.customernumber, oldbil.firstname, oldbil.lastname, oldbil.street, oldbil.zipcode, oldbil.city, oldbil.phone, oldbil.countryid, oldbil.stateid, oldbil.ustid, oldbil.additional_address_line1, oldbil.additional_address_line2, oldbil.title FROM DBOLDNAME.s_order_billingaddress AS oldbil JOIN s_order AS o ON o.idold = oldbil.orderID JOIN s_user AS u ON u.idold = oldbil.userID WHERE o.idold >=1;
INSERT INTO s_order_billingaddress_attributes (billingID) SELECT id FROM s_order_billingaddress WHERE id NOT IN (SELECT billingID FROM s_user_billingaddress_attributes);
INSERT INTO s_order_shippingaddress (userID, orderID, company, department, salutatiON, firstname, lastname, street, zipcode, city, countryID, stateID, additional_address_line1, additional_address_line2, title) 
SELECT u.id, o.id, oldship.company, oldship.department, oldship.salutatiON, oldship.firstname, oldship.lastname, oldship.street, oldship.zipcode, oldship.city, oldship.countryid, oldship.stateid, oldship.additional_address_line1, oldship.additional_address_line2, oldship.title FROM DBOLDNAME.s_order_shippingaddress AS oldship JOIN s_order AS o ON o.idold = oldship.orderID JOIN s_user AS u ON u.idold = oldship.userID WHERE o.idold >=1;
INSERT INTO s_order_shippingaddress_attributes (shippingid) SELECT id FROM s_order_shippingaddress WHERE id NOT IN (SELECT shippingid FROM s_order_shippingaddress_attributes);
ALTER TABLE `s_user` DROP `idold`;
ALTER TABLE `s_order` DROP `idold`;
ALTER TABLE `s_order_details` DROP `didold`;

System / Configuration

Deactivate non default plugins

This statement deactivates all plugins that have been uploaded in the plugin manager or have been installed from the community store. This is very useful for debugging errors, because with this you can easily find or exclude the influence of plugins for your behaviour.

First you create a temporar table with the orignal values to restore them later.


CREATE TABLE s_core_plugins_tmp LIKE s_core_plugins;
INSERT INTO `s_core_plugins_tmp` SELECT * FROM `s_core_plugins`;

Now you disable the plugins with this statement.


UPDATE `s_core_plugins` SET `active`= 0 WHERE `source` <> 'Default' AND author <> 'shopware AG';

For the restoring of the plugin values you put the data from the temporary table back in the original table and delete the no longer needed temporary table afterwards.


UPDATE s_core_plugins AS p JOIN s_core_plugins_tmp AS pt ON p.id = pt.id SET p.active = pt.active;
DROP TABLE s_core_plugins_tmp;

Resetting the database - deleting test data

Please mind that the data you delete might be connected and used in further elements and tables. For example customers and products are used and refered to in the orders, so deleting them might lead to a no longer existing connection in those.

Notice to foreign keys

According to the configuration and version of your MySQL server it may happen, that the foreign key check doesn't appear as a checkbox before you submit the SQL query, but nevertheless need to be deactivated. In this case, you need to enter this in the query, so set SET foreign_key_checks = 0; as new first line to disable the foreign key check and use SET foreign_key_checks = 1; as last line to reactivate it.


SET foreign_key_checks = 0;
TRUNCATE `s_user`;
TRUNCATE `s_user_addresses`;
TRUNCATE `s_user_addresses_attributes`;
TRUNCATE `s_user_attributes`;
TRUNCATE `s_user_billingaddress`;
TRUNCATE `s_user_billingaddress_attributes`;
TRUNCATE `s_user_shippingaddress`;
TRUNCATE `s_user_shippingaddress_attributes;
SET foreign_key_checks = 1;

Deleting customers

This query truncates all customer data from the user tables.


TRUNCATE `s_user`;
TRUNCATE `s_user_addresses`;
TRUNCATE `s_user_addresses_attributes`;
TRUNCATE `s_user_attributes`;
TRUNCATE `s_user_billingaddress`;
TRUNCATE `s_user_billingaddress_attributes`;
TRUNCATE `s_user_shippingaddress`;
TRUNCATE `s_user_shippingaddress_attributes`;

This query deletes all customers for a WHERE condition you have to add yourself.


DELETE aa.*, ad.*, ua.*, ba.*, ub.*, sa.*, us.*, u.* 
FROM `s_user` AS u 
LEFT JOIN s_user_addresses AS ad ON u.id = ad.user_id 
LEFT JOIN s_user_addresses_attributes AS aa ON ad.id = aa.address_id 
LEFT JOIN s_user_attributes AS ua ON u.id = ua.userID 
LEFT JOIN s_user_billingaddress AS ub ON u.id = ub.userID 
LEFT JOIN s_user_billingaddress_attributes AS ba ON ub.id = ba.billingID 
LEFT JOIN s_user_shippingaddress AS us ON u.id = us.userID 
LEFT JOIN s_user_shippingaddress_attributes AS sa ON us.id = sa.shippingID 
WHERE XXXX;

Deleting orders

With this query you truncate all order data from the database.


TRUNCATE `s_order`;
TRUNCATE `s_order_attributes`;
TRUNCATE `s_order_basket`;
TRUNCATE `s_order_basket_attributes`;
TRUNCATE `s_order_billingaddress`;
TRUNCATE `s_order_billingaddress_attributes`;
TRUNCATE `s_order_comparisons`;
TRUNCATE `s_order_details`;
TRUNCATE `s_order_details_attributes`;
TRUNCATE `s_order_documents`;
TRUNCATE `s_order_documents_attributes`;
TRUNCATE `s_order_esd`;
TRUNCATE `s_order_history`;
TRUNCATE `s_order_notes`;
TRUNCATE `s_order_shippingaddress`;
TRUNCATE `s_order_shippingaddress_attributes`;

With this query you can delete the orders for a WHERE condition you have to add yourself.


DELETE osa.*, os.*, odda.*, odd.*, oda.*, od.*, oba.*, ob.*, oa.*, o.* 
FROM s_order AS o LEFT JOIN s_order_attributes AS oa ON o.id = oa.orderID 
LEFT JOIN s_order_billingaddress AS ob ON o.id = ob.orderID 
LEFT JOIN s_order_billingaddress_attributes AS oba ON ob.id = oba.billingID 
LEFT JOIN s_order_details AS od ON o.id = od.orderID 
LEFT JOIN s_order_details_attributes AS oda ON od.id = oda.detailID 
LEFT JOIN s_order_documents AS odd ON o.id = odd.orderID 
LEFT JOIN s_order_documents_attributes AS odda ON odd.id = odda.documentID 
LEFT JOIN s_order_shippingaddress AS os ON o.id = os.orderID 
LEFT JOIN s_order_shippingaddress_attributes AS osa ON os.id = osa.shippingID
WHERE XXXX;

Deleting articles

By using this query you clear all product data from the database.


TRUNCATE `s_addon_premiums`;
TRUNCATE `s_articles`;
TRUNCATE `s_articles_also_bought_ro`;
TRUNCATE `s_articles_attributes`;
TRUNCATE `s_articles_avoid_customergroups`;
TRUNCATE `s_articles_categories`;
TRUNCATE `s_articles_categories_ro`;
TRUNCATE `s_articles_categories_seo`;
TRUNCATE `s_articles_details`;
TRUNCATE `s_articles_downloads`;
TRUNCATE `s_articles_downloads_attributes`;
TRUNCATE `s_articles_esd`;
TRUNCATE `s_articles_esd_attributes`;
TRUNCATE `s_articles_esd_serials`;
TRUNCATE `s_articles_img`;
TRUNCATE `s_articles_img_attributes`;
TRUNCATE `s_articles_information`;
TRUNCATE `s_articles_information_attributes`;
TRUNCATE `s_articles_notification`;
TRUNCATE `s_articles_prices`;
TRUNCATE `s_articles_prices_attributes`;
TRUNCATE `s_articles_relationships`;
TRUNCATE `s_articles_similar`;
TRUNCATE `s_articles_similar_shown_ro`;
TRUNCATE `s_articles_supplier`;
TRUNCATE `s_articles_supplier_attributes`;
TRUNCATE `s_articles_top_seller_ro`;
TRUNCATE `s_articles_translations`;
TRUNCATE `s_articles_vote`;
TRUNCATE `s_article_configurator_dependencies`;
TRUNCATE `s_article_configurator_groups`;
TRUNCATE `s_article_configurator_options`;
TRUNCATE `s_article_configurator_option_relations`;
TRUNCATE `s_article_configurator_price_variations`;
TRUNCATE `s_article_configurator_sets`;
TRUNCATE `s_article_configurator_set_group_relations`;
TRUNCATE `s_article_configurator_set_option_relations`;
TRUNCATE `s_article_configurator_templates`;
TRUNCATE `s_article_configurator_templates_attributes`;
TRUNCATE `s_article_configurator_template_prices`;
TRUNCATE `s_article_configurator_template_prices_attributes`;
TRUNCATE `s_article_img_mappings`;
TRUNCATE `s_article_img_mapping_rules`;
TRUNCATE `s_filter_articles`;

By using this query you can delete all products for a specific WHERE condition you have to add yourself.


DELETE aimr.*, aim.*, acta.*, actpa.*, actp.*, act.*, acor.*, av.*, atr.*, atop.*, ASiro.*, ASi.*, ar.*, apa.*, ap.*, an.*, aina.*, ain.*, aia.*, ai.*, aesds.*, aesda.*, aesd.*, adoa.*, ado.*, acs.*, acr.*, ac.*, acu.*, aa.*, ad.*, bro.*, a.*
FROM s_articles AS a 
LEFT JOIN s_articles_also_bought_ro AS bro ON a.id = bro.article_id 
LEFT JOIN s_articles_details AS ad ON a.id = ad.articleID 
LEFT JOIN s_articles_attributes AS aa ON ad.id = aa.articledetailsID 
LEFT JOIN s_articles_avoid_customergroups AS acu ON a.id = acu.articleID 
LEFT JOIN s_articles_categories AS ac ON a.id = ac.articleID 
LEFT JOIN s_articles_categories_ro AS acr ON a.id = acr.articleID 
LEFT JOIN s_articles_categories_seo acs ON a.id = acs.article_id 
LEFT JOIN s_articles_downloads AS ado ON a.id = ado.articleID 
LEFT JOIN s_articles_downloads_attributes AS adoa ON ado.id = adoa.downloadID 
LEFT JOIN s_articles_esd AS aesd ON a.id = aesd.articleID 
LEFT JOIN s_articles_esd_attributes AS aesda ON aesd.id = aesda.esdID 
LEFT JOIN s_articles_esd_serials AS aesds ON aesd.id = aesds.esdID 
LEFT JOIN s_articles_img AS ai ON a.id = ai.articleID 
LEFT JOIN s_articles_img_attributes AS aia ON ai.id = aia.imageID 
LEFT JOIN s_articles_informatiON AS ain ON a.id = ain.articleID 
LEFT JOIN s_articles_informatiON_attributes AS aina ON ain.id = aina.informatiONID 
LEFT JOIN s_articles_notificatiON AS an ON ad.ordernumber = an.ordernumber 
LEFT JOIN s_articles_prices AS ap ON a.id = ap.articleID 
LEFT JOIN s_articles_prices_attributes AS apa ON ap.id = apa.priceID 
LEFT JOIN s_articles_relatiONships AS ar ON a.id = ar.articleID 
LEFT JOIN s_articles_similar AS ASi ON a.id = ASi.articleID 
LEFT JOIN s_articles_similar_shown_ro AS ASiro ON a.id = ASiro.article_id 
LEFT JOIN s_articles_top_seller_ro AS atop ON a.id = atop.article_id 
LEFT JOIN s_articles_translatiONs AS atr ON a.id = atr.articleID 
LEFT JOIN s_articles_vote AS av ON a.id = av.articleID 
LEFT JOIN s_article_cONfigurator_optiON_relatiONs AS acor ON ad.id = acor.article_id 
LEFT JOIN s_article_cONfigurator_templates AS act ON a.id = act.article_id 
LEFT JOIN s_article_cONfigurator_template_prices AS actp ON act.id = actp.template_id 
LEFT JOIN s_article_cONfigurator_template_prices_attributes AS actpa ON actp.id = actpa.template_price_id 
LEFT JOIN s_article_cONfigurator_templates_attributes AS acta ON act.id = acta.template_id 
LEFT JOIN s_article_img_mappings AS aim ON ai.id = aim.image_id 
LEFT JOIN s_article_img_mapping_rules AS aimr ON aim.id = aimr.mapping_id
WHERE XXXX;

Resetting number ranges

In this version, the number ranges in Shopware are deleted and reapplied with default values.


DROP TABLE `s_order_number`;
 
CREATE TABLE IF NOT EXISTS `s_order_number` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `number` int(20) NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `desc` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=10 ;
 
INSERT INTO `s_order_number` (`id`, `number`, `name`, `desc`) VALUES
(1, 20000, 'user', 'Kunden'),
(2, 20000, 'invoice', 'Bestellungen'),
(3, 20000, 'doc_1', 'Lieferscheine'),
(4, 20000, 'doc_2', 'Gutschriften'),
(5, 20000, 'doc_0', 'Rechnungen'),
(6, 10000, 'articleordernumber', 'Artikelbestellnummer  '),
(7, 10000, 'sSERVICE1', 'Service - 1'),
(8, 10000, 'sSERVICE2', 'Service - 2'),
(9, 110, 'blogordernumber', 'Blog - ID');

Resetting statistics

Some statistics are obtained from the orders and customer data. The other values can be deleted with the following query:


TRUNCATE `s_statistics_currentusers`;
TRUNCATE `s_statistics_pool`;
TRUNCATE `s_statistics_referer`;
TRUNCATE `s_statistics_search`;
TRUNCATE `s_statistics_visitors`;

Resetting categories and shopping worlds

With this version, all categories as well as created shopping worlds will be removed. The main category will be automatically reset to "Deutsch" with the system ID 3.


TRUNCATE `s_categories`;
TRUNCATE `s_categories_attributes`;
TRUNCATE `s_categories_avoid_customergroups`;
TRUNCATE `s_emotion`;
TRUNCATE `s_emotion_attributes`;
TRUNCATE `s_emotion_categories`;
TRUNCATE `s_emotion_element`;
TRUNCATE `s_emotion_element_value`;
 
INSERT INTO `s_categories` (`id`, `parent`, `path`, `description`, `position`, `left`, `right`, `level`, `added`, `changed`, `metakeywords`, `metadescription`, `cmsheadline`, `cmstext`, `template`, `active`, `blog`, `external`, `hidefilter`, `hidetop`, `mediaID`, `product_box_layout`, `meta_title`, `stream_id`) VALUES
(1, NULL, NULL, 'Root', 0, 0, 0, 0, '2012-07-30 15:24:59', '2012-07-30 15:24:59', NULL, NULL, NULL, NULL, NULL, 1, 0, NULL, 0, 0, NULL, NULL, NULL, NULL),
(3, 1, NULL, 'Deutsch', 0, 0, 0, 0, '2012-07-30 15:24:59', '2012-07-30 15:24:59', NULL, NULL, NULL, NULL, NULL, 1, 0, NULL, 0, 0, NULL, NULL, NULL, NULL);
 
INSERT INTO `s_categories_attributes` (`id`, `categoryID`, `attribute1`, `attribute2`, `attribute3`, `attribute4`, `attribute5`, `attribute6`) VALUES
(1, 3, NULL, NULL, NULL, NULL, NULL, NULL);