Complex delete statements in MySQL

Agent_Dwarf

Hello everyone,

I have tried to solve this problem, but can't. Basically, I want to get rid of messages that have expired (or aren't "saved") in a few queries, but with the table structure I have, I can't seem to figure out how.

Here's my db structure:

conversations -

conversation_id (auto_incremented, primary key)
expirationdate (timestamp)
saved (integer, 0 or 1)
//other fields you don't need to know

messages

conversation_id
message text

If it wasn't clear before, I want to delete messages that their corresponding conversation has reached it's expiration date has expired. I was thinking about a query like:

DELETE FROM messages, conversations WHERE messages.conversation_id = conversations.conversation_id AND conversations.expirationdate < NOW() AND conversations.saved = 0

But that doesn't really make sense, and it gives me an error when trying to do it. (BTW, anyone have an easy to read tutorial on JOINs?)

So, any help would be appreciated. Feel free to modify the table structure, if necessary.

Many thanks

tomkleijkers

Please copy the error in this threat. It cleares something up. You can get a error-warning using mysql_error() in php:
so

<?php
mysql_query("DELETE ....") or die(mysql_error());
?>

gives a error-message when an error occures.

tomkleijkers

Usually it should be possible to use < now() on a timestamp.

Agent_Dwarf

Originally posted by tomkleijkers
Please copy the error in this threat. It cleares something up. You can get a error-warning using mysql_error() in php:
so

<?php
mysql_query("DELETE ....") or die(mysql_error());
?>

gives a error-message when an error occures.

You have an error in your SQL syntax near ' conversations WHERE messages.conversation_id = conversations.conversation_id AN' at line 1

Which leads me to believe you can't do DELETE FROM table, anothertable where .... type syntax :\

mattr

You cannot use joined deletes in MySQL

alimac

you haven't joined the tables for your where clause to work and also I haven't seen this syntax before for deleting from multiple tables, try 2 delete statements with the first having the join and the second merely just deleting from conversations:

mysql_query("DELETE FROM messages join conversations WHERE messages.conversation_id = conversations.conversation_id AND conversations.expirationdate < NOW() AND conversations.saved = 0",$db);

mysql_query("DELETE FROM conversations WHERE conversations.expirationdate < NOW()",$db);

good luck

alimac

sorry I should have said you need to set a variable with now() as teh situation may arise where you delete more than one conversation id with the second delete (if it is a microsecond older than the first - unlikely but possible) so set a variable e.g.

$now = mysql_query("select now()",$db);

mysql_query("DELETE FROM messages join conversations WHERE messages.conversation_id = conversations.conversation_id AND conversations.expirationdate < '$now' AND conversations.saved = 0",$db);

mysql_query("DELETE FROM conversations WHERE conversations.expirationdate < '$now'",$db);

tomkleijkers

I don't think the post above works, does it?

Maybe you better solve the problem this way:
First find the conversations which have expired and then delete the post.

It's not nessecary to use $now=.... now() is allowed in SQL.

tomkleijkers

$result=mysql_query("SELECT * FROM conversations WHERE expirationdate < now() AND saved='0'") or die(mysql_error());
while($fetch=mysql_fetch_array($result)){
     $id=$fetch['conversation_id'];
     mysql_query("DELETE FROM messages WHERE conversation_id='$id'") or die(mysql_error());
     mysql_query("DELETE FROM conversations WHERE conversation_id='$id'") or die(mysql_error());
};

I think this will do.

Agent_Dwarf

Originally posted by tomkleijkers

$result=mysql_query("SELECT * FROM conversations WHERE expirationdate < now() AND saved='0'") or die(mysql_error());
while($fetch=mysql_fetch_array($result)){
     $id=$fetch['conversation_id'];
     mysql_query("DELETE FROM messages WHERE conversation_id='$id'") or die(mysql_error());
     mysql_query("DELETE FROM conversations WHERE conversation_id='$id'") or die(mysql_error());
};

I think this will do. [/B]

What if you have to delete 100 conversations? Won't that take a long time? Or a mere second or two?

tomkleijkers

It could take some time, when you delete 100 messages it will take about 0,5 sec. (depending on your server). So don't worry, it's quick enough! 😉