Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161

    Unanswered: Delete From Select

    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
    MySQL 5.1

  2. #2
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Yes there is a way to delete like that...

  3. #3
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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...

  4. #4
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    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
    Last edited by anjanesh; 06-11-07 at 08:16.
    MySQL 5.1

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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!!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    That's a might big hole I fell in back there!
    To remove confusion completely
    Code:
    WHERE p.products_id NOT IN (215,305)
    George
    Home | Blog

  9. #9
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    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.

    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
    MySQL 5.1

  10. #10
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •