Re: UPDATE based on value in another table
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]
Re: UPDATE based on value in another table
Dan Jones [EMAIL PROTECTED] wrote: How do I update a table to remove orphaned references to a second table? I've deleted rows in the second table, which has a unique auto_increment key. The first table now has references to keys that no longer exist. I need to update the first table, setting the value to NULL where the referenced key no longer exists. Something like: 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. If you have MySQL server version 4.0.4 or newer, you can do something like: UPDATE table1 LEFT JOIN table2 ON table1.table2ID=table2.ID SET table1.table2ID=NULL WHERE table2.ID IS NULL. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UPDATE based on value in another table
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]
Re: UPDATE based on value in another table
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 delete all references, you need to set up a Trigger. Or else, just use multiple queries, to either delete the referenced columns, or to set values in them to NULL. -- [ Rajesh Kumar ] __ Meet the guy at http://www.meetRajesh.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UPDATE based on value in another table
How do I update a table to remove orphaned references to a second table? I've deleted rows in the second table, which has a unique auto_increment key. The first table now has references to keys that no longer exist. I need to update the first table, setting the value to NULL where the referenced key no longer exists. Something like: 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
