[SOLVED] DELETE FROM (using "in") only deletes one row

[Xeoncross]

I have been working with this "IN" clause but I can't get this statement to kill more than one database row.

 

<?php
$query = 'DELETE FROM `comments` WHERE `id` in (\''. mysql_real_escape_string($id_list). '\') LIMIT '. count($ids); 
?>

>
>DELETE * FROM `comments` WHERE `id` in ('85,84,82') LIMIT 3;
>

 

I only deletes one row instead of all three. (MySQL 4+)

 

 

 

[Wildbug]

Let me guess, `id` is some kind of INT column type, right?  So why are you feeding it a string, '85,84,82'?  Try removing those quotes.

[Xeoncross]

Yep, that is what it was. 

 

I tried it without quotes the first time but I go an error...

[fenway]

Yep, that is what it was. 

 

I tried it without quotes the first time but I go an error...

What error?

[Xeoncross]

Well, now I am having trouble with another "in" statement. Also, I am afraid I don't remember what error that was fenway. 

 

DELETE FROM `content`, `posts` WHERE `posts.id` = `content.id` AND `content.id` in (56) LIMIT 1

[Illusion]

Try this

DELETE FROM `content`, `posts` USING `content`,`posts` WHERE `posts.id` = `content.id` AND `content.id`=56 LIMIT 1

or

DELETE  `content`, `posts` FROM `content`,`posts` WHERE `posts.id` = `content.id` AND `content.id`=56 LIMIT 1

 

 

[Xeoncross]

Well, I need it to be an "IN" statement because I am deleting many ids usually...

I don't have any trouble with single ids. 

 

 

DELETE FROM `content`, `posts` WHERE `posts.id` = `content.id` AND `content.id` in (56,60,49,34) LIMIT 4

DELETE FROM `content`, `posts` WHERE `content.id` in (56,60,49,34) LIMIT 4

[Xeoncross]

Bump 

[akitchin]

illusion was right - when specifying multiple tables in the DELETE statement, you have to explicitly differentiate between tables being used solely for the WHERE clause and those you actually want to DELETE from.  have a look in the manual for syntax, but you can use DELETE table1, table2 FROM table1, table2 or DELETE FROM table1, table2 USING table1, table2

[teng84]

i guess you can delete the record form two table if you do something like joining before get the item to delete

 

like

DELETE FROM table1,table2 where tqable1.x=table2.x and  table1.x in ()

 

i guess something like that will work

 

 

[Xeoncross]

Ok, I finally got it.

 

 

DELETE FROM `posts`, `content` USING `posts`, `content` WHERE content.id = posts.id AND posts.id in (63,62)

Deleted rows: 4 (Query took 0.0011 sec)