Re: Please help - About doing a delete.
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
Re: Please help! - About doing a delete.
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
Please help! - About doing a delete.
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
