Link to home
Start Free TrialLog in
Avatar of danmcd0913
danmcd0913

asked on

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
Avatar of awking00
awking00
Flag of United States of America image

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

Based on this you can insert your details on Address table.
Avatar of danmcd0913
danmcd0913

ASKER

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.  
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.
ASKER CERTIFIED SOLUTION
Avatar of danmcd0913
danmcd0913

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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.