I would like to add a record for all people in an address table that do not have a home address

Guru's

I have a table of addresses with both mailing and home addresses identified by a personid foreign key.  I would like to create a home address for the the persons that do not already have one.

addressid (pk) personid(fk)    addtype   street                zip            . . . . . . .
1                      1                     home        abc street
2                      1                     mailing      xyz street
3                      2                     mailing      123 street
danmcd0913Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

awking00Information Technology SpecialistCommented:
From your example, are you looking to add another row for personid 2, creating a home address as hte mailing address? For example, would the new row look like the following?
addressid (pk) personid(fk)    addtype   street                zip            . . . . . . .
4                      2                     home        123 street
0
Swadhin Ray Commented:
>> I would like to create a home address for the the persons that do not already have one.

Do you want the list of the records to be identified first and then insert the records.
So that you can insert the home address manually or it need to be done by any UI , if from UI then what are the input that you want to insert like columns information  .

Please try to answer awking00 question too.  and provide some more information.
0
Swadhin Ray Commented:
Based on your question here is one senario which you can look to get the details of the persons who dont have the HOME address on address table:

create table person 
(
personid number primary key,
person_name varchar2(100));


insert into person values(1,'SAM');
insert into person values(2,'DRAGON');
insert into person values(3,'ROGER');
insert into person values(4,'TALE');
COMMIT;

create table address
(
addressid number primary key,
personid number ,
addtype varchar2(50),
street varchar2(50),
zip number);

ALTER TABLE ADDRESS ADD 
      CONSTRAINT FK_ADDRESS_PERSON_NUM
      FOREIGN    KEY (personid)
      REFERENCES PERSON(personid) ; 
      
      
INSERT INTO ADDRESS(ADDRESSID,PERSONID,addtype,street,zip)
VALUES (1,1,'HOME','XYZ STREET',123);

INSERT INTO ADDRESS(ADDRESSID,PERSONID,addtype,street,zip)
VALUES (2,1,'MAILING','ABC STREET',123);

INSERT INTO ADDRESS(ADDRESSID,PERSONID,addtype,street,zip)
VALUES (3,2,'MAILING','XYZ STREET',5637);

COMMIT;

Open in new window


Query to get the details:

SELECT P.PERSONID,
  P.person_name
FROM PERSON P
WHERE NOT EXISTS
  (SELECT 1 FROM ADDRESS WHERE PERSONID=P.PERSONID AND addtype ='HOME'
  );

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Swadhin Ray Commented:
Based on this you can insert your details on Address table.
0
danmcd0913Author Commented:
The table has a little over 600K records and only 50K have both mailing and home records I'd like to copy the mailing addresses for the other 550K and make those home addresses for those records.  
0
Swadhin Ray Commented:
is the address type is only change you required or anything else can also change ?
If others get change then its need to be inserted manually for each records and if you have the home address type details stored in other table then you can write a procedure to insert the records.
0
danmcd0913Author Commented:
Here's my solution, it seems a bit clunky and I'm positive there's an easier way but this worked.
CREATE TABLE STG_HOME_ADD AS (SELECT * FROM TBL_ELIG_APPC_ADDRESS);
CREATE TABLE STG_MAILING_ADD AS (SELECT * FROM TBL_ELIG_APPC_ADDRESS);

DELETE STG_HOME_ADD WHERE DMGTYPEID = 5;
DELETE STG_MAILING_ADD WHERE DMGTYPEID = 2;

CREATE INDEX IDX_HOME_PERSON ON STG_HOME_ADD (PERSONID);
CREATE INDEX IDX_MAILING_PERSON ON STG_MAILING_ADD (PERSONID);

ALTER TABLE STG_MAILING_ADD ADD (HOMEADDRESSALREADYEXISTS VARCHAR2(1 ));

UPDATE STG_MAILING_ADD SET HOMEADDRESSALREADYEXISTS = 'Y'
WHERE PERSONID IN(SELECT PERSONID FROM STG_HOME_ADD);

DELETE STG_MAILING_ADD WHERE HOMEADDRESSALREADYEXISTS = 'Y'

INSERT INTO NYENROLL.STG_MAILING_ADD
(ADDRESSID, PERSONID, DMGTYPEID, ADDLINE1, ADDLINE2, CITY, STATEID, EFFSTARTDATE, EFFENDDATE, CREATEUSERID, CREATEDATE, LASTMODUSERID, LASTMODDATE, COUNTYNAME, ACTIVEFLAG, ZIP, ZIP4)
SELECT
ADDRESSID, PERSONID, DMGTYPEID, ADDLINE1, ADDLINE2, CITY, STATEID, EFFSTARTDATE, EFFENDDATE, CREATEUSERID, CREATEDATE, LASTMODUSERID, LASTMODDATE, COUNTYNAME, ACTIVEFLAG, ZIP, ZIP4
FROM STG_HOME_ADD;

UPDATE STG_MAILING_ADD SET DMGTYPEID = 2;

DELETE TBL_ELIG_APPC_ADDRESS WHERE DMGTYPEID = 2;

INSERT INTO NYENROLL.TBL_ELIG_APPC_ADDRESS
(ADDRESSID, PERSONID, DMGTYPEID, ADDLINE1, ADDLINE2, CITY, STATEID, EFFSTARTDATE, EFFENDDATE, CREATEUSERID, CREATEDATE, LASTMODUSERID, LASTMODDATE, COUNTYNAME, ACTIVEFLAG, ZIP, ZIP4)
SELECT
ROWNUM + 10000000, PERSONID, DMGTYPEID, ADDLINE1, ADDLINE2, CITY, STATEID, EFFSTARTDATE, EFFENDDATE, CREATEUSERID, CREATEDATE, LASTMODUSERID, LASTMODDATE, COUNTYNAME, ACTIVEFLAG, ZIP, ZIP4
FROM STG_MAILING_ADD;
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
danmcd0913Author Commented:
I'm accepting my solution as the solution because no one presented a better solution.  I'm going to assume I just asked the question poorly as typically I get immediate and accurate solutions when posting a question on this site.  I'm giving myself a grade of C because i'm new and based on the amount of code necessary to get to a solution I'm again going to assume that there was a much easier way it just wasn't presented.
0
awking00Information Technology SpecialistCommented:
Your solution, in addition to being "clunky", appears to insert the same address_id for the new home address record that exists in the corresponding (i.e. same person_id) mailing address. Given that the address_id is a primary key, is that what you really want since it will no longer be unique? How is the address_id determined in the first place? There is certainly a better means of accomplishing what you need, if you would just provide a little more description of how the data looks before the inserts and how it should look afterwards.
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
Oracle Database

From novice to tech pro — start learning today.