Oracle Update one to many

Hello all,

I have a table that holds all of my investigations for each individual. This table has a column called account_id. The account id is associated to one company. I noticed my previous worker update my case table with the wrong id. So I need to correct this. My investigation table as an invest_id and the account_id. My case table has multiple records for the account_id due to different users. I need to update the many records in my case table with the correct id from the investigation table. Please see sample data below for better detail.

invest table:
id       company     account_id
1        abc              12455

case table:
rpt_mth     user     case_id
2012-02     jane     12455
2012-02     john     12455
2012-02     mike    12455

I need the case table to read like this:
rpt_mth     user     case_id
2012-02     jane     1
2012-02     john     1
2012-02     mike    1

The case table has over 200k records over the last two years. So I need an update that will cross that period. I have tried the query below but am getting an error on updating many records from one.

update case_tbl
set case_id = invest_tbl.id
where case_tbl.case_id = invest_tbl.account_id;


Any help would be great.

Thanks,
Jeff
woodjeAsked:
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.

HomerTNachoCheeseCommented:
update case_tbl
set case_id = 1
where case_tbl.case_id = 12455;

I think all you might have been doing was finding records with 12455 and re-entering 12455 again.  Or, you were ending up with no records to change.

All you need to do is look for 12455 and set it to 1 - no need to cross reference other tables' data I think.
0
woodjeAuthor Commented:
HomerTNachoCheesem

Thank you for the quick response. However the data i provided is only a small sampling. I am dealing with updating over 200,000 records for server rpt_mth's and several different accounts I can't do them one at a time. Can you suggest a way to modify my query to pull the current account id from the case_tbl and pull the id from the investigation table and update my case_id in my case_tbl with the id from the invest_tbl.
0
slightwv (䄆 Netminder) Commented:
Can you use a simple PL/SQL block?

drop table myinvest purge;
create table myinvest(id number, account_id number);

insert into myinvest values(1,12455);
insert into myinvest values(2,11111);
commit;

drop table mycase purge;
create table mycase(myuser varchar2(10), case_id number);

insert into mycase values('jane',12455);
insert into mycase values('john',12455);
insert into mycase values('mike',12455);
insert into mycase values('bill',11111);
commit;

begin
	for i in (select id,account_id from myinvest) loop
		update mycase set case_id=i.id where case_id=i.account_id;
	end loop;
	commit;
end;
/

Open in new window

0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

HainKurtSr. System AnalystCommented:
maybe this

UPDATE case_tbl c
   SET case_id =
          (SELECT id
             FROM invest i
            WHERE i.account_id = c.case_id)

Open in new window

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
HomerTNachoCheeseCommented:
I don't have Oracle installed anywhere to figure it out and get the syntax correct.

What you could try is create a select query that pulls all your data together for starters, where all fields you mentioned in your original post are present.

I am only running Access right now, so if I wrote it for Access SQL, it would look like something this:

UPDATE case_tbl
INNER JOIN invest_tbl ON case_tbl.case_id = invest_tbl.account_id
SET case_tbl.case_id = invest_tbl.id

Potential problem is that you cannot update the field that is joined.

Probably not the best way of doing things, but if you created a copy of the case_id field and all of its data into a field called case_id_old, then you could join on that field instead if you ran into that problem.
0
woodjeAuthor Commented:
HainKurt,

Your idea looked great but I get the error More than one value was returned by a subquery.
0
slightwv (䄆 Netminder) Commented:
Looks like you might have duplicate account ids in the invest table.

select account_id from invest group by account_id  having count(account_id) > 1;
0
woodjeAuthor Commented:
slightwv,

Yes looks like I have about 16k of the 200k that do have dups for the account_id. If it is not one thing it is another.
0
slightwv (䄆 Netminder) Commented:
My pl/sql loop will run but it will grap the last account_id it comes to which probably isn't what you want...

Once you get everything cleaned up:  Constraints, constraints, constraints...  Then you'll not get back into this mess.
0
HainKurtSr. System AnalystCommented:
if you have multiple ids for case_id what is your solution?

invest table:
id       company     account_id
1        abc              12455
2        abc              12455

case table:
rpt_mth     user     case_id
2012-02     jane     12455
2012-02     john     12455
2012-02     mike    12455

???
0
HainKurtSr. System AnalystCommented:
you can try one of these:

random pickup:

UPDATE case_tbl c
   SET case_id =
          (SELECT id
             FROM ((  SELECT id, ROWNUM rn
                        FROM invest i
                       WHERE i.account_id = c.case_id))
            WHERE rn = 1)

min id pickup

UPDATE case_tbl c
   SET case_id =
          (SELECT MIN (id)
             FROM invest i
            WHERE i.account_id = c.case_id)


max id pickup

UPDATE case_tbl c
   SET case_id =
          (SELECT MAX(id)
             FROM invest i
            WHERE i.account_id = c.case_id)
0
woodjeAuthor Commented:
This worked with some modification to use max invest_id and filtering on dates. But your sample pointed me in the right direction to get the job done. Thanks.
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
Enterprise Software

From novice to tech pro — start learning today.