delete rows from tableA that are in tableB

jx12345 · #1 (**permalink**) 12-02-11, 06:48

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

r937 · #2 (**permalink**) 12-02-11, 07:30

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

jx12345 · #3 (**permalink**) 12-02-11, 07:46

thanks rudy, that first one works fine... how come it doesn't like the aliases?

r937 · #4 (**permalink**) 12-02-11, 07:58

Quote:

Originally Posted by jx12345

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

jx12345 · #5 (**permalink**) 12-02-11, 08:15

the join looks like voodoo to me i'm too scared to use it.

Internet Services

Web Development

Digital Marketing

Consumer Tech