On Sat, 2003-08-23 at 22:19, Rajesh Kumar wrote: > Dan Jones unknowingly asked us: > > > UPDATE table1 SET table2ID = NULL WHERE table1.table2ID NOT FOUND IN > > table2.ID; > > > > The NOT FOUND IN isn't SQL, of course, but I'm not sure what should go > > there. > > In MySql, its 'NOT IN' > > and not > > 'NOT FOUND IN'.
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. UPDATE Title SET AuthorID=NULL WHERE AuthorID NOT IN Author.AuthorID; This gives a syntax error "...near Author.AuthorID." UPDATE Title SET AuthorID=NULL WHERE AuthorID NOT IN (Author.AuthorID); This seems to treat (Author.AuthorID) as a list of data rather than a field, and sets every Title.AuthorID to NULL. 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 General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
