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
sweetazAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kelvin SparksCommented:
You cannot delete from customers while there is a corresponding record in address. Either upgrade the table relationships to allows cascade deletes between these two tables or delete the address records first, then the customer records.


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

Open in new window

0
sweetazAuthor Commented:
thanks HuyBD b ut the query you suggest only seems to remove the records from customer table and not the address table.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Kevin CrossChief Technology OfficerCommented:
Kelvin's first comment is correct.  You cannot delete from both.  HuyBD's suggestion helps you with syntax on proper delete using the relationship to do the filtering, so in your case it appears that CUSTOMER table has the key to finding records to delete so you want to do this (unless you enable cascading delete from CUSTOMER to ADDRESS table.
-- delete matching addresses
DELETE a
FROM dbo.ADDRESS a 
INNER JOIN dbo.CUSTOMER c ON c.home_address = a.address_code
WHERE c.customer_code LIKE '%[^0-9]%'
 
-- delete matching customers
DELETE c
FROM dbo.CUSTOMER c
WHERE c.customer_code LIKE '%[^0-9]%'

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sweetazAuthor Commented:
thanks mwvisa1 this actually did occur to me lastnight. I should wait 24 hours before commenting! I blame monday-itis.  
0
Kevin CrossChief Technology OfficerCommented:
:)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.