Link to home
Start Free TrialLog in
Avatar of sweetaz
sweetaz

asked on

Cannot delete on inner join

I have a couple of bad records that i need to delete from my customer table and also remove the corresponding address record.
I can return all the rows I need to delete with;

select *      
from       dbo.CUSTOMER inner join            dbo.ADDRESS
on            customer.home_address = address.address_code
where      customer_code      like      '%[^0-9]%'

but changing the query above to

delete *      
from       dbo.CUSTOMER inner join            dbo.ADDRESS
on            customer.home_address = address.address_code
where      customer_code      like      '%[^0-9]%'

does not work not sure what I am missing.
Thanks
SOLUTION
Avatar of Kelvin Sparks
Kelvin Sparks
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sweetaz
sweetaz

ASKER

thanks HuyBD b ut the query you suggest only seems to remove the records from customer table and not the address table.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sweetaz

ASKER

thanks mwvisa1 this actually did occur to me lastnight. I should wait 24 hours before commenting! I blame monday-itis.