MySQL 1443: what does it mean?

Question

I'm trying to do an update, in MySQL 5.0, of the form:

update mytable.myfield t
set f = 'blah'
where t.id in (select v.id from myview v where ...);

MySQL tells me:

ErrorNr. 1443
The definition of table 'v' prevents operation UPDATE on table 't'.

The MySQL docs list this error, but (as usual) don't say what it means. Can anyone shed some light on this for me? I only reference the view in the subquery, and I only reference the table in the main query, and I don't know why these would prevent the update. The only thing I found with google is a bug in the MySQL bug db related to triggers, but (AFAIK) there are no triggers in my db.

Answer 1

I think the view myview must be based on the table mytable, so that as it makes changes to myfield, it loses track of what's in the view and therefore makes for an illegal update.

I would recommend looking at the definition of myview, so that you can write your query without referencing it. Then you may be able to work it out.

Alternatively, dump the list of ids to a temporary table and use that for your subquery.

Rob

Answer 2

Thanks Rob, I had a similar problem and the solution is that table to be updated can´t be used in the view.