• Replies 8 replies
  • Subscribers 336 subscribers
  • Views 115 views
  • Users 0 members are here
Related

SQL code to remove contact point purposes from inactive addresses

Anne M Robichaux
$organization

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 a
JOIN [impresario].[dbo].[TX_CONTACT_POINT_PURPOSE] as c
ON a.address_no = c.contact_point_id
WHERE inactive = 'Y'

Thanks!
Anne

Parents
  • $organization

    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'

    select * from TX_CONTACT_POINT_PURPOSE

    --commit transaction

    rollback transaction

  • $organization in reply to Gawain Lavers

    I get the following error message: Msg 4104, Level 16, State 1, Line 9
    The multi-part identifier "c.contact_point_id" could not be bound.

  • $organization in reply to Anne M Robichaux

    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:

    1. Do some queries (maybe counts) to show the data I'm about to change
    2. Begin transaction
    3. Run the insert/update/delete
    4. Do more queries to show how the data has changed
    5. Roll back transaction

    I do that, check my numbers, and then finally switch out the rollback line for a commit line.

Reply
  • $organization in reply to Anne M Robichaux

    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:

    1. Do some queries (maybe counts) to show the data I'm about to change
    2. Begin transaction
    3. Run the insert/update/delete
    4. Do more queries to show how the data has changed
    5. Roll back transaction

    I do that, check my numbers, and then finally switch out the rollback line for a commit line.

Children