I'm trying to do an update, in MySQL 5.0, of the form:
我正在嘗試在MySQL 5.0中進行以下格式的更新:
update mytable.myfield t
set f = 'blah'
where t.id in (select v.id from myview v where ...);
MySQL tells me:
MySQL告訴我:
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.
MySQL文檔列出了這個錯誤,但(像往常一樣)不說它意味着什么。任何人都可以為我闡明這一點嗎?我只引用子查詢中的視圖,我只引用主查詢中的表,我不知道為什么這些會阻止更新。我在谷歌找到的唯一一件事就是與觸發器相關的MySQL bug數據庫中的一個錯誤,但是(AFAIK)我的數據庫中沒有觸發器。
1 个解决方案
#1
9
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.
我認為視圖myview必須基於表mytable,因此當它對myfield進行更改時,它會丟失跟蹤視圖中的內容,從而導致非法更新。
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.
我建議查看myview的定義,以便您可以編寫查詢而無需引用它。然后你可以解決它。
Alternatively, dump the list of ids to a temporary table and use that for your subquery.
或者,將id列表轉儲到臨時表,並將其用於子查詢。
Rob
搶