How To Change How Many Rows Can Edit Sql

Author: Marcus Gesing

The general mode to change shop information is either via the backend or via a data import. In some cases information tin also exist direct inserted, changed or deleted by SQL (Structured Query Language). Such SQL statements tin be executed without boosted software using the shop backend (see System > Maintenance). In general it is recommended to create a database fill-in in society to exist able to restore the old information in case of accidents.

Merely there is another mode: straight editing of database values using SQL Server Management Studio (SSMS). This fashion is illustrated in the following example. The chore is to alter the social club of all options of the specification attribute "Farbe" (colour).

Outset I demand the ID of the specification aspect "Farbe". The ID is the unchangeable principal key of a information tape. I become the value either via the data table "SpecificationAttribute" or via the Shop backend nether Itemize > Specification attributes > Farbe. In this example the ID is ii.

Next, I search for the SpecificationAttributeOption table in our database using the SSMS Object Explorer. Information technology contains the options of all specification attributes. By right-clicking on the table proper name I select the command "Edit Pinnacle 200 Rows". Past 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. Simply instead of the first 200 options I want to change the specification attribute "Farbe". To do this, I correct-click in the data window and select "Pane > SQL". The SQL argument for loading the options is and so displayed in the upper part of the window and I tin change it to my needs. This requires some nuts in SQL, but don't worry, it's not very complicated. At the moment the statement looks like this:

SELECT TOP (200) Id, SpecificationAttributeId, Proper name, DisplayOrder, Alias, NumberValue, MediaFileId, Colour
FROM SpecificationAttributeOption

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

SELECT Id, SpecificationAttributeId, Proper name, DisplayOrder, Alias, NumberValue, MediaFileId, Color
FROM SpecificationAttributeOption

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

SELECT SpecificationAttributeId, Proper name, DisplayOrder
FROM SpecificationAttributeOption

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

SELECT SpecificationAttributeId, Proper name, DisplayOrder
FROM SpecificationAttributeOption
WHERE SpecificationAttributeId = 2

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

SELECT SpecificationAttributeId, Name, DisplayOrder
FROM SpecificationAttributeOption
WHERE SpecificationAttributeId = two
Social club Past Name

For a descending order the statement would be Society Past Proper name DESC. To execute the updated SQL argument I make a right click in the statement or data window and select "Execute SQL".

In gild to keep the options alphabetically sorted in the shop, I simply take to add ascending values in the column "DisplayOrder". It is advisable to leave gaps betwixt the values to make information technology easier to maintain the sorting when adding new colours.

Download:
SQL Server Management Studio (SSMS)

Source: https://smartstore.com/en/edit-database-values-using-sql-server-management-studio#:~:text=By%20right%2Dclicking%20on%20the,rows%20or%20options%20are%20loaded.

Posted by: hernandezdencen.blogspot.com