Hello,
I'm trying to use SQL to remove contact point purposes from addresses that are marked inactive. I've come up with a query that pulls these addresses, but I am having trouble figure out the DELETE command. Any ideas?
Here's my code so far:SELECT * FROM [impresario].[dbo].[VS_ADDRESS] as aJOIN [impresario].[dbo].[TX_CONTACT_POINT_PURPOSE] as cON a.address_no = c.contact_point_idWHERE inactive = 'Y'
Thanks!Anne
First: Always do this in Test first! Then, something like this:
select * from TX_CONTACT_POINT_PURPOSE
begin transaction
delete cpp
from TX_CONTACT_POINT_PURPOSE as cpp
inner join T_ADDRESS as a on a.address_no = c.contact_point_id
and a.inactive = 'Y'
--commit transaction
rollback transaction
I get the following error message: Msg 4104, Level 16, State 1, Line 9The multi-part identifier "c.contact_point_id" could not be bound.
Sorry, I was just copying from your script and forgot that I changed the alias, so presumably that would be cpp.contact_point_id.
Just to be sure, you know that the script I gave you will not actually make the change? It's set to roll back the transaction as currently written. I like to do these database change scripts this way:
I do that, check my numbers, and then finally switch out the rollback line for a commit line.
That is how I do them in my dreams. In reality, I am far more reckless.
One mistake I've made with this for, say, a delete, would be to query "where thing to be deleted". I then look and see, okay, before here are a bunch of rows and after, no rows, great! Not realizing that I've also deleted a bunch of things I didn't intend to.
I tried running it without the rollback and now my Test system can't find postal addresses and is crashing out. Oops! Running a Live to Test copy.
Did you include the commit? If not then the transaction is still open and the tables are likely locked to other processes.