MY Sql same table delete in clause

I want to do what seems like a simple delete in mysql but am having some trouble. I want to do this:

delete from table where col_name not in (select distinct col_name from table);

This table does not have a unique key. When I try this, I get the error:

You can't specify target table for update in from clause; ErrorNr. 1093

mysql community 5.1

Is there a way i can save the output from this query ->

select distinct col_name from table;

into temp. table and use it in the delete query?

~thx

 

delete from table where col_name not in (select a.col_name from (select distinct col_name from table) a);

It should work.

Edit : sorry, I misunderstood the question, and only focus on the SQL Error. The probleme to delete duplicate line with no unique key isn't answered by the above request.

LINQ with MySQL

LINQ with MySQL

LINQ to SQL from Microsoft supports only their SQL Server. How about other popular DBMS such as MySQL ?
I investigate the first result returned from Google using "LINQ with MySQL" query: DbLinq. It's an opensource project which provides "LINQ to SQL" for other databases include MySQL, PostgreSQL, Oracle and SQLite.
However, the project is still at its very beginning stage and I think it will take some time before we can use it in production environment, IMHO.
Here is my setup of DbLinq with MySQL

• Visual Studio 2008 Professional
• MySQL 5
• DbLinq 0.18
• Windows Vista

1. I first have my database prepared, its name is "Items".
2. Run DbMetal which is a tool comes with DbLinq in "build" folder. I use the following command for my MySQL server. I have user root with no password.

C:\Downloads\DbLinq-0.18\DbLinq-0.18\build>DbMetal /server:127.0.0.1 /database:items /provider:MySQL /user:root /code:Items.cs
DbLinq Database mapping generator 2008 version 0.18.0.0
for Microsoft (R) .NET Framework version 3.5
Distributed under the MIT licence (http://linq.to/db/license)
>>> Reading schema from MySQL database
<<<>

3. You will have a C# code file, Items.cs in my case, automatically generated. This file contains C# classes generated from database schema and the mapping attributes.
4. You may want to compile the project now. I found some errors in my cases:

• There is no type specified for fields of MySQL's Enum type. You can fix this easily by insert C#'s "string" type. In my case: private _gender; to private string _gender;
• EntitySet cannot be resolved. Change every occurances to System.Data.Linq.EntitySet instead.

If you can compile your project, you can proceed to the next step.
5. Here is my code for querying from MySQL database. From my setup, it's an ASP.NET web application with a single GridView on the Default.aspx page.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

protected void Page_Load(object sender, EventArgs e)

{

string connStr =

"server=127.0.0.1; user id=root; database=items";

Durian.Items db = new Durian.Items(

new MySqlConnection(connStr));

var products = from p in db.Item

where p.UnitPriceCredit > 10

select new { p.ItemID, p.Name, p.Remaining };

GridView1.DataSource = products;

GridView1.DataBind();

}

6. Run and see the result : )