On Monday, August 25, 2003, at 03:26 PM, Dan Jones wrote:
[snip]
To make this a tad clearer, I have two tables: Title and Author. Author
consists of the Author's name and an AuthorID. Title includes an
AuthorID from the Author table. Some of the Author's have been deleted
from the Author table. I want to remove any AuthorID entries from the
Title table that no longer exists in the Author table.
[snip]
My SQL book, a generic reference not specific to any particular database, indicates the way to do this is via UPDATE with a subquery, like so:
UPDATE Title SET AuthorID=NULL WHERE NOT IN (SELECT AuthorID FROM Author);
This gives me a syntax error "...near SELECT AuthorID FROM Author)"
Does MySQL not support UPDATE with subqueries or am I screwing up the syntax somehow?
MySQL does not support UPDATE with subqueries. I think it's on the list somewhere. Like a prerelease version may have it.
Would this work for you?
SELECT Title.AurthorID AS title_author, Author.AuthorID AS author_author FROM title_author LEFT JOIN author_author WHERE author_author IS NULL;
There's your list of AuthorID's that have been deleted from the Author Table, but that still exist in the Title table.
If possible, you could go through and then do the update programatically based on this list?
The big difference being that you have already figured out that these are the ones you need to remove instead of having to check each and every authorID....
-Cameron Wilhelm
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
