Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2010
    Posts
    53

    Unanswered: delete rows from tableA that are in tableB

    I wont to delete all rows in customer_products that are for customers who are in the customers_archive table but i can't seem to see what i'm doing wrong.

    delete from customer_products cp
    where cp.customer_id in
    (select c.customer_id from customer_archive c)

    this gives me an error

    although this

    select * from customer_products cp
    where cp.customer_id in
    (select c.customer_id from customer_archive c)

    works fine???

    any help much appreciated.

    ta

    jim

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this --
    Code:
    delete from customer_products 
    where customer_id in
    (select customer_id from customer_archive)
    or this --
    Code:
    delete customer_products 
      from customer_products 
    inner
      join customer_archive
        on customer_archive.customer_id = customer_products.customer_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2010
    Posts
    53
    thanks rudy, that first one works fine... how come it doesn't like the aliases?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jx12345 View Post
    how come it doesn't like the aliases?
    not allowed in the syntax, i guess

    you might find the joined delete faster than the IN subquery

    admittedly not a big deal if you need to do this only once
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2010
    Posts
    53
    the join looks like voodoo to me i'm too scared to use it.

Posting Permissions

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