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
精彩评论