Devart Blog

Mind data modifications via Data Editor in dbForge Studio for MySQL

Posted by on January 30th, 2012

Since one of our users has encountered a problem when modifying data via Data Editor, we decided to bring such a situation to our users’ attention.

Let’s assume you have the following table:

CREATE TABLE dept(
  DeptNo INT(10),
  DName VARCHAR(14),
  loc VARCHAR(13),
  Salsum FLOAT
);

Data-Editor-1

Let’s execute the following query in the SQL document:

SELECT DName, loc FROM dept WHERE DeptNo = 20;

Now if you change any of the values of the first record in Data Editor, the changes will be applied to all records of the original table that have ‘DName’ = ‘Research’ and ‘loc’ = ‘Dallas’.

Data-Editor-2

It happens because when dbForge Studio for MySQL generates the internal UPDATE statement, only the fields returned by the previous query are included in the WHERE clause of the UPDATE statement. In our example DeptNo = 20 that was stated in the WHERE clause of the SELECT statement was not included in the internal UPDATE statement as there was no the ‘DeptNo’ field in the grid when modifying data.

So that if you’re planning to modify data that will be returned as a result of the SELECT statement in the grid, we suggest you to make sure that all the fields that are specified in the WHERE clause of the SELECT statement are also specified in the SELECT clause of the statement.

In our example, you have to specify the ‘DeptNo’ field in the SELECT clause of the statement.

SELECT DeptNo, DName, loc FROM dept WHERE DeptNo = 20;

Now if you modify any of the values of the records in Data Editor, the above actions will exclude accidental changes in the records that are not displayed in the grid.

Data-Editor-3

Finally, to avoid such problems that may occur when modifying data that was returned as a result of the ‘SELECT’ statement inside of which the ‘WHERE’ clause is used, make sure you’re specifying the ‘*’ in the ‘SELECT’ clause or at least all the columns listed in the ‘WHERE’ clause of the statement.

Leave a Reply