Mysql pdate query error

Question

I am trying to update my mysql table "upproj" using Visual Studio 2010 (c#). I want to execute the following query :

string Query = " update tlog.upproj set summ=(select sum(grade) from tlog.upproj group by ams having count(*) =2); ";

But I get error

"You can't specify target table 'upproj' for update in FROM clause".

When I execute the previous query on Mysql Query browser I don't get any error, but my project needs to execute this query from visual studio.

Is there any way to fix that?

What exactly is the purpose of your `UPDATE` query? If that *were* to work, it would update every row in the table as there is no `WHERE` clause set on it. — newfurniturey, Sep 30 '13 at 18:24
And see [this answer](http://stackoverflow.com/a/14302701/1385896) on a similar question. — Filipe Silva, Sep 30 '13 at 18:28
The purpose of update is to set values to summ column. I do this though visual studio cause ,instead of '2' in having count subquery, i use a variable that is given on vs project. And sorry for my bad english. — GreatGatsby, Sep 30 '13 at 18:29

score 1 · Accepted Answer · answered Sep 30 '13 at 18:30

As the error hints at, you can't specify the table you're updating in the UPDATE statement's FROM clause (in the sub-query); the same is true if you were to try to use it in a WHERE clause.

You can accomplish the task of "using the table you're updating" if you use a JOIN though.

Based on the schema inferred by your existing query, the ams field is unique to a set grouping and you can, therefore, add that as part of your clause.

Try the following (untested):

UPDATE
    tlog.upproj AS u1
    JOIN (SELECT ams, SUM(grade) AS grade_sum FROM tlog.upproj GROUP BY ams HAVING COUNT(*) = 2) AS u2
        ON u1.ams = u2.ams
SET
    summ = u2.grade_sum;

This should update each record for ams with the total sum of grade for that same ams. If a specific ams doesn't have 2 entries, it isn't updated.

Mysql pdate query error

1 Answers1