Dev-Insider: Editing database values using SQL Server Management Studio (SSMS)

Author: Marcus Gesing

The general way to change shop data is either via the backend or via a data import. In some cases data can also be directly inserted, changed or deleted by SQL (Structured Query Language). Such SQL statements can be executed without additional software using the shop backend (see System > Maintenance). In general it is recommended to create a database backup in order to be able to restore the old data in case of accidents.

But there is another way: direct editing of database values using SQL Server Management Studio (SSMS). This way is illustrated in the following example. The task is to change the order of all options of the specification attribute "Farbe" (color).

First I need the ID of the specification attribute "Farbe". The ID is the unchangeable primary key of a data record. I get the value either via the data table "SpecificationAttribute" or via the Shop backend under Catalog > Specification attributes > Farbe. In this case the ID is 2.

Next, I search for the SpecificationAttributeOption table in our database using the SSMS Object Explorer. It contains the options of all specification attributes. By right-clicking on the table name I select the command "Edit Top 200 Rows". By the way, the number of rows loaded with this command can be changed by the option "Tools > Options > SQL Server Object Explorer > Commands > Value for Edit top <n> Rows command". If 0 is entered, all rows or options are loaded.

In the data window that has opened, options can now already be changed. But instead of the first 200 options I want to change the specification attribute "Farbe". To do this, I right-click in the data window and select "Pane > SQL". The SQL statement for loading the options is then displayed in the upper part of the window and I can change it to my needs. This requires some basics in SQL, but don't worry, it's not very complicated. At the moment the statement looks like this:

SELECT TOP (200) Id, SpecificationAttributeId, Name, DisplayOrder, Alias, NumberValue, MediaFileId, Color
FROM SpecificationAttributeOption

First I remove "TOP (200)" because I don't want to edit the first 200 rows.

SELECT Id, SpecificationAttributeId, Name, DisplayOrder, Alias, NumberValue, MediaFileId, Color
FROM SpecificationAttributeOption

After SELECT, all field names of the table SpecificationAttributeOption are listed, separated by commas. These may well change in the context of updates of SmartStore.NET. I'm only interested in name (the option value), DisplayOrder (the order) and for checking purposes the SpecificationAttributeId field, which contains the ID of the associated specification attribute. So remove all other field names from the statement.

SELECT SpecificationAttributeId, Name, DisplayOrder
FROM SpecificationAttributeOption

After FROM, you enter the name of the table whose data is to be loaded. This remains the same of course. But I do not want to load all options (I had removed the Top 200 before), but only those of the specification attribute "Farbe". For this purpose I add a where-clause to only load color options by using the specification attribute ID for "Farbe" (in this case, as mentioned above, the 2).

SELECT SpecificationAttributeId, Name, DisplayOrder
FROM SpecificationAttributeOption
WHERE SpecificationAttributeId = 2

Last but not least, I would like to see the options sorted alphabetically in ascending order of its name. For this I append an order-by-statement with the desired field name.

SELECT SpecificationAttributeId, Name, DisplayOrder
FROM SpecificationAttributeOption
WHERE SpecificationAttributeId = 2
ORDER BY Name

For a descending order the statement would be ORDER BY Name DESC. To execute the updated SQL statement I make a right click in the statement or data window and select "Execute SQL".

In order to keep the options alphabetically sorted in the shop, I simply have to add ascending values in the column "DisplayOrder". It is advisable to leave gaps between the values to make it easier to maintain the sorting when adding new colours.