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
