Hi sunny,
Yes it is a sad scenario. But hopefully this feature would be available in
the near future.
If you cannot use a programming language the only way AFAIK is to use
temporary tables.
Create temporary table temptable SELECT messages.*, if(main.topicid is
null,1,0) deleteflag
FROM messages
LEFT OUTER JOIN main
ON messages.topicid=main.topicid
WHERE main.topicid is null;
You have now the complete data of the original table but with an extra
column deleteflag
with either 1 or 0 as its value.
Now you can delete the rows of temptable by issuing the command
Delete from temptable where deleteflag = 1;
Alter table temptable drop deleteflag;
Delete from messages;
Insert into messages select * from temptable;
drop table temptable;
This is like taking food through nostrills but one without a mouth has no
better alternative
Regards.
Anvar.
At 12:17 AM 30/03/2002 +0000, you wrote:
>So you're saying there's no actual DELETE statement for this? There is no
>way in hell I can write any SQL to do that?? Thats sucks....... :(
>
>Thanks for the example, but how do I run it? I've only used PHP for taking
>information out of a database and other simple MySQL queries so while your
>Perl example kinda makes sense, I don't understand how to work it. Do I
>just put that in a file and run open the file in a browser?
>
>Thanks!
>
>sunny
>
>
>
>At 08:12 29/03/02 -0700, Rodney Broom wrote:
>>Good morning Sunny,
>>
>>
>>From: sunny <[EMAIL PROTECTED]>
>>
>> > ...substituting SELECT with DELETE doesn't work :(
>>
>>That's right, that's how MySQL works. And it doesn't support sub-queries
>>for this case, either. So you can't say:
>>
>> delete from table where field in (select field from other_table)
>>
>>I'd suggest doing this from another language, like Perl. For instance:
>>
>> $list_list = $dbh->selectall_arrayref(qq{
>> SELECT messages.topicid
>> FROM messages
>> LEFT OUTER JOIN main
>> ON messages.topicid=main.topicid
>> WHERE main.topicid is null
>> });
>>
>> for my $row ( @{$list_list} ) {
>> $dbh->do(qq{DELETE FROM messages WHERE topicid = $row->[0]});
>> }
>>
>>
>>Note, my example is rough, and not tuned for performance. But you get the
>>idea. Hollar if you still need a hand with this.
>>
>>
>>
>>---
>>Rodney Broom
>>Programmer: Desert.Net
>>
>>
>>
>>
>>---------------------------------------------------------------------
>>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
---------------------------------------------------------------------
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
