Delete From Select

anjanesh · #1 (**permalink**) 06-11-07, 03:09

Hi

I there a way to delete like this ?

Code:

DELETE * FROM `products` WHERE `products_id` IN (
   SELECT p.`products_id`
   FROM `products` p, `products_suppliers` ps
   WHERE p.`products_id` = ps.`products_id`
   AND (p.`products_id` <> 215 AND p.`products_id` <> 305)
)

?

Thanks

aschk · #2 (**permalink**) 06-11-07, 04:48

Yes there is a way to delete like that...

aschk · #3 (**permalink**) 06-11-07, 04:49

Incidently this part of your query :

Code:

AND (p.`products_id` <> 215 AND p.`products_id` <> 305)

Will ALWAYS be true AND will still allow product ids 215 and 305 to pass through...

anjanesh · #4 (**permalink**) 06-11-07, 07:07

Code:

   SELECT p.`products_id`
   FROM `products` p, `products_suppliers` ps
   WHERE p.`products_id` = ps.`products_id`
   AND (p.`products_id` <> 215 AND p.`products_id` <> 305)

return all ids except 215 and 305. The DELETE part should delete the rest keeping 215 and 305 in the table.

I seem to get this err-msg
#1093 - You can't specify target table 'table-name' for update in FROM clause

r937 · #5 (**permalink**) 06-11-07, 07:36

Quote:

Originally Posted by aschk

...Will ALWAYS be true

i don't think that's right

it would be, if it had said

Code:

AND (p.`products_id` <> 215 OR p.`products_id` <> 305)

with OR, everything is going to be not equal to one or the other

but with AND, i believe it works as intended

anjanesh, would you mind saying in words what you're trying to do?

if i interpret your first query, it says "delete all products that have a supplier and that aren't 215 or 305"

do you have products without a supplier? and you want to keep those? if so, why?

gvee · #6 (**permalink**) 06-11-07, 07:40

I think aschk is getting at the fact that one product has only one product_id. There is no product with both 215 AND 305 as their id.

Code:

p.`products_id` <> 215 AND p.`products_id` <> 305

I think it should look more like this - note the use of joins and the OR operator

Code:

SELECT	p.products_id
FROM	products p
LEFT JOIN products_suppliers ps
	ON p.products_id = ps.products_id
WHERE	p.products_id <> 215
OR	p.products_id <> 305

To complete your delete statement

Code:

DELETE FROM products
WHERE	product_id IN
	(
	SELECT	p.products_id
	FROM	products p
	LEFT JOIN products_suppliers ps
		ON p.products_id = ps.products_id
	WHERE	p.products_id <> 215
	OR	p.products_id <> 305
	)

HTH ~George

r937 · #7 (**permalink**) 06-11-07, 07:55

Quote:

Originally Posted by georgev

There is no product with both 215 AND 305 as their id.

well, of course

but i fear you have fallen into the same trap

this --

... p.`products_id` <> 215 AND p.`products_id` <> 305

is perfectly okay

whereas what you wrote --

... p.`products_id` <> 215 OR p.`products_id` <> 305

will accept every single row in the table!!!

gvee · #8 (**permalink**) 06-11-07, 08:28

That's a might big hole I fell in back there!
To remove confusion completely

Code:

WHERE p.products_id NOT IN (215,305)

anjanesh · #9 (**permalink**) 06-12-07, 13:20

Quote:

if i interpret your first query, it says "delete all products that have a supplier and that aren't 215 or 305"

Correct. Actually delete all products whose ids arent 215 or 305 and supplier_id is 1. I forgot to mention AND supplier_id = 1 but thats fine.

Quote:

do you have products without a supplier? and you want to keep those? if so, why?

No - all products are assigned a supplier. I dont want to keep the deleted product_ids in the other tables - I'll do the manual cascading later - just wanted to know if I could delete these ids using SQL - without using a server-side language to iterate through.

So is there any way to delete this way ?

Code:

DELETE FROM `table1` WHERE `id` IN
 (SELECT `id` FROM `table1` WHERE `id` <> 215 AND `id` <> 305)

which'll delete all ids except 215 and 305

shammat · #10 (**permalink**) 06-13-07, 01:25

Quote:

Originally Posted by anjanesh

So is there any way to delete this way ?

Code:

DELETE FROM `table1` WHERE `id` IN
 (SELECT `id` FROM `table1` WHERE `id` <> 215 AND `id` <> 305)

which'll delete all ids except 215 and 305

Why not:

Code:

DELETE FROM table1 WHERE id NOT IN (215,305)

Internet Services

Web Development

Digital Marketing

Consumer Tech