Update records in a relation from a single field select

[qupear]

Usually DBMS protects a DBA from updating records when there are a select with not all fields from a relation. But dbForge allows update operation even when you select only one field from a relation.

Suppose you have a relation "Users" with PK "id" of type int. And you have a relation "Computers" which have a field "user_id" that corresponds to field "id" in relation "Users". There may be multiple records in a "Computers" with same "user_id" value. Now only for records which was created on date 2018-07-18 in a "Computers" where user_id=12345678 you need to change user_id value to 12345679. Then suppose that you execute such a query:

Code: Select all

select c.user_id from computers c where c.user_id=12345678 and c.date_begin='2018-07-18';

This query selects only two records. But in a "Computers" there are 102 records where user_id=12345678 and different date_begin values. And by now there are no computers where user_id=12345679.

Now magic trick happens. In those two selected cells you type 12345679 instead of 12345678. After committing this update you won't have any records in a relation "Computers" that represents user_id=12345678. Instead of two there will be 102 records with user_id=12345679.

I'm not saying that this logic of update is wrong. May be in documentation there's even a topic about that. But I think that by default if a DBA selects not all fields from a relation and changing some values then there must be some warning dialogbox about possible data loss.

BTW this all is about version 7.4.201 of MySQL Studio.

Thank you.

[alexa]

We will review this and will answer you as soon as possible.

[qupear]

By the way, if you add to select list at least ID field, then update works as expected. And I also checked what happens in another DBMS in similar update: it throws an error "Can't update attribute '...' - attributes of key '`...`.....`PRIMARY`' are missing in result set". You could just add this restriction and issue will be resolved. Thank you.

[alexa]

We will fix this issue in one of the next product versions and will notify you once it's available for downloading.