• Replies 7 replies
  • Subscribers 338 subscribers
  • Views 96 views
  • Users 0 members are here
Related

SQL code to switch fields on certain constituent addresses?

Elizabeth Marvin
$organization

We've recently discovered that a large number of the addresses on our Institutional constituents were entered weirdly, with the street address in Street Address 1 and the organizational name in Street Address 2 (see pic for example). So when we create addresses from these entries, we end up with:

John Doe

1 Main St

Business Name

City, State Zip

If I isolate into a list all of the records that need to be updated, is there a SQL process that could automatically switch Street Addresses 1 & 2 for all of those records? Or are we stuck updating these one by one? 

Thanks, from a grateful SQL beginner!

Parents
  • $organization

    Hi Elizabeth, 

    Did you get this figured out yet? If not, I think it would be pretty easy to fix with SQL. This is a quick script I just whipped up that should get the job done. You'll want to make sure to test it first in TEST. You'll just need to create a static list first with all the accounts that need fixing:

    --------

    drop table if exists #add_fix

    create table #add_fix (customer_no int, address_no int, street1_new varchar(255), street2_new varchar(255))

    insert into #add_fix (customer_no, address_no, street1_new, street2_new)

    select
    lc.customer_no,
    ad.address_no,
    street2,
    street1
    from T_LIST_CONTENTS lc
    join T_ADDRESS ad on lc.customer_no = ad.customer_no and primary_ind = 'Y'
    where lc.list_no = #### --enter the list number here for the list you created with all accounts you need to fix

    select * from #add_fix --for testing purposes

    begin transaction

    update T_ADDRESS
    set street1 = af.street1_new, street2 = af.street2_new
    from #add_fix af
    join T_ADDRESS ad on af.address_no = ad.address_no
    where ad.address_no = af.address_no

    --this is for testing purposes to see the results of the update before committing the change to the database
    select * from T_ADDRESS ad1
    join T_LIST_CONTENTS lc1 on ad1.customer_no = lc1.customer_no where lc1.list_no = #### and primary_ind = 'Y'  --enter the list number from above

    Rollback transaction --when you have tested this script and you are happy with the results, change the word "rollback" to "commit"

    ----------

    Hopes this helps, feel free to reach out if you have any questions, or something isn't working the way you expect. Good luck!

    David

Reply
  • $organization

    Hi Elizabeth, 

    Did you get this figured out yet? If not, I think it would be pretty easy to fix with SQL. This is a quick script I just whipped up that should get the job done. You'll want to make sure to test it first in TEST. You'll just need to create a static list first with all the accounts that need fixing:

    --------

    drop table if exists #add_fix

    create table #add_fix (customer_no int, address_no int, street1_new varchar(255), street2_new varchar(255))

    insert into #add_fix (customer_no, address_no, street1_new, street2_new)

    select
    lc.customer_no,
    ad.address_no,
    street2,
    street1
    from T_LIST_CONTENTS lc
    join T_ADDRESS ad on lc.customer_no = ad.customer_no and primary_ind = 'Y'
    where lc.list_no = #### --enter the list number here for the list you created with all accounts you need to fix

    select * from #add_fix --for testing purposes

    begin transaction

    update T_ADDRESS
    set street1 = af.street1_new, street2 = af.street2_new
    from #add_fix af
    join T_ADDRESS ad on af.address_no = ad.address_no
    where ad.address_no = af.address_no

    --this is for testing purposes to see the results of the update before committing the change to the database
    select * from T_ADDRESS ad1
    join T_LIST_CONTENTS lc1 on ad1.customer_no = lc1.customer_no where lc1.list_no = #### and primary_ind = 'Y'  --enter the list number from above

    Rollback transaction --when you have tested this script and you are happy with the results, change the word "rollback" to "commit"

    ----------

    Hopes this helps, feel free to reach out if you have any questions, or something isn't working the way you expect. Good luck!

    David

Children