Possible Issues with Data Editor

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 (
  DName VARCHAR(14),
  Loc VARCHAR(13),
  SalSum FLOAT
  );

INSERT INTO Dept(DeptNo, DName, Loc, SalSum)
  VALUES
     (10, 'Accounting', 'New York', 8750),
     (20, 'Research', 'Dallas', 10875),
     (30, 'Sales', 'Chicago', 9400),
     (40, 'Operations', 'Boston',NULL),
     (50, 'Research', 'Dallas', 10875),
     (20, 'Accounting', 'New York', 8750)

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’.

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 ‘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 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.

Finally, to avoid such issues while 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.

Want to Find out More?

Overview

Take a quick tour to learn all about the key benefits delivered by dbForge Studio for MySQL.

All features

Get acquainted with the rich features and capabilities of the Studio in less than 5 minutes.

Request a demo

If you consider employing the Studio for your business, request a demo to see it in action.

Ready to start using dbForge Studio for MySQL?

Download a free 30-day trial

• Previous
• Next