开发者

Mysql Update errors on keys

Update AAA.master A, BBB.images B, BBB.content C 
set A.caption = B.image_txt where C.content_id_key = A.media_id

I get the following error although I am certain they are both primary keys. Do开发者_如何学编程 they have to have a PK->FK relationship? I dont think so. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

Thanks


The solution is in the error.

But first of all you need to rewrite your query.
Repeat after me:

I must not use implicit where joins, because they are confusing!

Rewrite the update query into this:

Update AAA.master a
INNER JOIN BBB.images b ON (a.someid = b.someid) #<<-- your error is here 
INNER JOIN BBB.content c ON (C.content_id_key = A.media_id)
SET A.caption = B.image_txt WHERE ........   #<<-- and also here

The first error you've made is that there's no join criterion between table A and B. Second error is that in strict mode you need to have a where clause.
And you did not I did no you did not have a where clause, you just had a join criterion disguised as a where clause.

Also you're still a join criterion short and the real where clause short.

This is why implicit joins suck so bad


it might be because you're not selecting a record from the table B in the WHERE part

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜