well i can do the select that way, but i was wondering how the following delete command would only pick up the rows in the select. isn't there a way of imbedding delete in the select statement?
can anyone else help as well please? thanks sunny At 19:46 14/03/02 -0500, Jim Philips wrote: >To select the rows you are looking for, you can use: > >select * from messages >left join topics on topics.topic_id=messages.topic_id >where topics.topic_id is null; > >This works fine for selecting. But for some reason the corresponding SQL >for a delete fails: > >mysql> delete from messages > -> left join topics on topics.topic_id=messages.topic_id > -> where topics.topic_id is null; >ERROR 1064: You have an error in your SQL syntax near 'left join topics >on topics.topic_id=messages.topic_id > >Anybody know how to make the delete statement work? > > > > >On Thu, 2002-03-14 at 15:57, sunny wrote: > > > > How do i do a select first and then delete those rows? sorry.. im just > > confused. i tried that mysql, but it didn't work... gave me an error :( > > > > Any ideas? > > > > thanks! > > > > /sunny > > > > > > > > At 20:54 13/03/02 -0500, you wrote: > > >What you need is a delete with a left join. Something like this: > > > > > >delete from messages > > >left join messages on topics.topic_id=messages.topic_id > > >where topics.topic_id is null; > > > > > >Try it first using a select instead of a delete to be sure it captures > > >the right rows. But this ought to work. > > > > > >On Wed, 2002-03-13 at 17:53, sunny wrote: > > > > Hi everyone, > > > > > > > > I've got a really annoying problem with mysql, and I was hoping someone > > > > could help. > > > > I've got 2 tables in Mysql: > > > > > > > > Table 1 - "Topics" > > > > > > > > | topic_id | name | subject | comment | > > > > --------------------------------------------------------------------- > > > > | 1 | john | weather| the weather today.... blah blah| > > > > > > > > > > > > Table 2 - "Messages" > > > > > > > > |message_id | topic_id | name | subject | comment | > > > > --------------------------------------------------------------------- > > > > | 12 | 1 | billy | yes it ...| i totally > agree....| > > > > > > > > Basically, in the messages table, each row has its own unique id, > but also > > > > a topic_id column which relates to the topic_id in the Topics table. > > > > What I want to do is delete all messages in the message table for > which > > > the > > > > relating topic_id has been already deleted in the Topics table. For > > > > example, if the row with topic_id 5 was deleted in Topics table, I > want to > > > > make sure there are no messages in the Messages table which have a > > > topic_id > > > > of 5. > > > > > > > > Please please, any help would be greatly appreciated. I'm running MySQL > > > > 3.23.46 on UNIX. > > > > > > > > TIA! > > > > > > > > sunny > > > > > > > > > > > > --------------------------------------------------------------------- > > > > Before posting, please check: > > > > http://www.mysql.com/manual.php (the manual) > > > > http://lists.mysql.com/ (the list archive) > > > > > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > > > To unsubscribe, e-mail > > > <[EMAIL PROTECTED]> > > > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
