sql newbi - need to copy address_3 from inactive dup record to active address_3

Question

Hi community,

I have a table with about 200k addresses. Some are inactive others are active. the inactive ones are duplicates of my active ones. Some of the active ones do not have the address_3 (community names) but the some inactive duplicates do have address_3. I need to being the inactive address_3 into the duplicate match that is active. I'm struggling with the structure. Can anyone help?

Answer 1

Assuming that there is a field called address_1 or something that we can group by, I would:

• Create an empty table (by reusing the DDL from your existing table).
• Populate that table with rows where address_3 IS NOT NULL and make sure you GROUP BY the appropriate column (example shown below).

I would run the SELECT without the INSERT to make sure that you have the correct data.

INSERT INTO second_addresses_table
SELECT
    a.address_1, 
    -- #other columns...
    a.address_3, 
    TRUE -- #default to Active = TRUE
FROM 
    first_addresses_table a
WHERE
    a.address_3 IS NOT NULL
GROUP BY 
    a.address_1,
    -- #other columns...
    a.address_3