MySQL Bugs: #46916: Error updating data

Bug #46916	Error updating data
Submitted:	25 Aug 2009 12:12	Modified:	26 Sep 2009 7:20
Reporter:	Antonio Lopez	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	6.0.3	OS:	Windows
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
When updating data through Entity model in a table without primary key, the update fails with "Every derived table must have its own alias".

How to repeat:
First, create a table on the console and add a register:

create table testbug(id int not null unique, varchar(255) data);
insert into testbug(id, data) values(1, "test");

Then create a project with the entity model created from the database containig the table and add:

bugEntities ents = new bugEntities();
testbug[] bug = ents.testbug.ToArray();
bug[0].data = "Crash";
ents.SaveChanges();

Logging the MySQL query shows this result:

220 Query       UPDATE (SELECT
      `testbug`.`data`, 
      `testbug`.`id`
      FROM `testbug` AS `testbug`) SET `data`='Crash' WHERE `id` = 1

Suggested fix:
Remove the select for the update, it will be faster and will cause no error, or add the alias to the table.

Hi Antonio and thanks for your report.

Can you please attach complete test case (including DDL/DML needed to set database up) demonstrating this error.

Side note; updating tables without primary key is never a good idea, what if you have FLOAT field in table? You can not match them exactly...

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".