Home / Blog /Code Snippets /MySQL /Quick Tips /Relational Databases / A MySQL Update with a Sub-Select
Here’s a MySQL scriptlet you won’t want to forget:
“select child.id from child left join parent on (child.parent_id=parent.id) where child.id is not null and parent.id is null;”
According to Dennis Haney, the original poster of this scriptlet (as found in his comment on this MySQL dev page here) you will get the error “ERROR 1216: Cannot add or update a child row: a foreign key constraint fails”, if a foreign key you are attempting to add is referencing a parent id that is NULL. So, this scriptlet helps you find the problematic NULL identities. Once the NULL is replaced with a valid value you can proceed to finish putting your foreign keys in place.
